What is the alternative to INTERSECT ?

[Glenn Rowe]

I have tried several queries and can't figure out the correct syntax. For the sake of an example lets assume I have the following table....

table name is "records"

userid       field         value
==========   ===========   ===========
1000         City          Avonmore
1000         Province      Ontario
1000         Gender        Male
1001         City          Avonmore
1001         Province      Ontario
1001         Gender        Female
1002         City          Cornwall
1002         Province      Ontario
1002         Gender        Male
1003         City          Cornwall
1003         Province      Ontario
1003         Gender        Female
1004         City          Avonmore
1004         Province      Ontario
1004         Gender        Male

How do I get the list of userids of people that are male and live in Avonmore, Ontario ?

I tried this...

select userid from records where field="City" and value="Avonmore"
INTERSECT
select userid from records where field="Province" and value="Ontario"
INTERSECT
select userid from records where field="Gender" and value="Male";

...but it doesn't work.... I have read that mysql does not support the INTERSECT command so, how would I write the above query?



Edited 1 time(s). Last edit at 10/24/2010 02:26PM by Glenn Rowe.