Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: Query without subselect

    Hi,

    let say, there is one table named 'CLASSES' with 2 columns ('person' and 'class'):

    +=====================+
    | person | class |
    +=====================+
    | person 1 | math |
    | person 1 | physics |
    | person 1 | chemistry |
    | person 2 | math |
    | person 2 | philosophy |
    | person 3 | physics |
    | person 3 | chemistry |
    | person 3 | literature |
    +=====================+

    This means that every person takes particular classes. How to make a query that should do the following:

    GIVE ME ALL PERSONS THAT TAKES PHYSICS AND CHEMISTRY

    Because i work with MySql 3.23, subselects are not allowed. I was thinking to use temporary tables, but that doesn't seem like an elegant and effective solution.

    Can you help me with this? Any help will be appreciated. Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    91

    Re: Query without subselect

    select distinct person
    from classes
    where class = 'physics' or class = 'chemistry'

    or you might want this:

    select distinct person
    from classes c1, classes c2
    where c1.class = 'physics'
    and c2.class = 'chemistry'
    and c1.person = c2.person

    Is this a homework assignment?

  3. #3
    Join Date
    Dec 2003
    Posts
    8
    Well, thanks for the reply.

    The first query:

    select distinct person
    from classes
    where class = 'physics' or class = 'chemistry'

    isn't the query that i'm looking for. This query will return result with persons that take one of the classes (or both). My goal is result with persons that take strictly the both classes.

    The second query is the correct one. But, i'm not quite sure about the efficiency of that type of query. In this case, everything is fine. But, if i need to supply more conditions (not just 'physics' and 'chemistry'), let say, 100 other classes (this is fictious ), then i should "create" 100 "instances" of the same table 'CLASSES':

    select distinct person
    from classes c1, classes c2, . . . . . , classes c100
    . . . . . . . . . . . .

    As far as i know, joining 100 tables is very expensive operation, both in processing time and memory.

    So, is there more convenient and flexible solution to this problem?

    -- And, no , this is not a homework assignment, although seems like that because of the example. I have more complex problem to solve, but this simple example can show me the right way --

  4. #4
    Join Date
    Nov 2003
    Posts
    91
    In general, joins are a very expensive operation.
    In this case, however, additional joins should not add
    much time to the query because the result set monotonically
    decreases for each new join that is added.
    (That is, the number of rows returned actually gets smaller
    each time you add an additional join.)
    This is the direct converse to what normally happens when
    a join is performed because most of the time joins increase
    the size of the result set.
    So I suggest you just see how this plays out, and optimize it
    only if it turns out in the future you have to.
    Remember, premature optimization is the root of all evil.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    using GROUP BY, and summing the number of occurrences of certain situations in the group, you can make arbitrarily complex situations easy to solve, while the query remains quite efficient

    for example, list all people who have taken at least two of physics, chemistry, and math, but not theater --
    Code:
    select person
      from classes
     group
        by person
    having sum(case 
                 when class = 'physics'
                 then 1 else 0 end
              )     
         + sum(case 
                 when class = 'chemistry'
                 then 1 else 0 end
              )     
         + sum(case 
                 when class = 'math'
                 then 1 else 0 end
              )               
         > 1
       and sum(case 
                 when class = 'theater'
                 then 1 else 0 end
              )     
         = 0
    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •