[SOLVED] Querying a resultset

[desertlord]

I'm having a horrible time trying to figure out how to do this.

I have a form with several search options. I'm trying to build the search criteria. Here's a very simplified example of what I'd like to do:

 

I have a table with data like this:

 

userid qid   answer

24         8   Female

32         8   Male

50         8   Female

24         29   Arizona

32         29   California

50         29   California

 

I would like to query this table to find userids that are Female (24 and 50) AND from California (32 and 50). So the result should be userid 50. I can't do this in one query (unless I'm mistaken) so I would need to first get a list of userids who are females, like this:

 

$query1 = "SELECT userid from myTable WHERE qid = 8 AND answer = 'Female'"

 

But then, after getting that result set, how do I go through the results to find users from California?

 

If I'm looking at this all wrong please point me in the right direction.

 

 

[fenway]

Is there a UID in this table?

Where is the "8" coming from?

[desertlord]

Is there a UID in this table?

Where is the "8" coming from?

 

UID would be the userid field. The 8 is coming from the qid field

Thanks.

[fenway]

UID = unique ID -- I mean, a unique identifier for each row.

 

And you're missing my point -- how do you know to look for 8 specifically?

[desertlord]

UID = unique ID -- I mean, a unique identifier for each row.

 

And you're missing my point -- how do you know to look for 8 specifically?

 

My apologies. There is a unique id, answerid.

8 comes from a multiple select box in the search form. In this case there are options Male, Female and all. There's actually 7 select boxes in total.

[fenway]

ok... then try:

 

SELECT userid 
from myTable as t1
inner join myTable as t2 using ( userid )
WHERE t1.qid = 8 AND t1.answer = 'Female'
AND t2.qid = 29 AND t2.answer = 'California'

[desertlord]

Oh, very nice. That should do it.

Thank you very much.