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.

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?

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 --

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.

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 --rudy
http://r937.com/