reducing the number of queries

[c0rnf3d]

im sure theres a more efficient way to do what i need to, but i know not how to do it..

 

my table consists of 3 columns: user_id, question_id, answer_id

 

i want to query and find a user_id (or user_ids) that have my desired answer_ids (4+ desired answers)

 

basically i want to say something like "select * from answers where answer_id = '1' and answer_id='2' and answer_id = '3' and answer_id='4' " ---- and have this return to me a list of all users that have the desired answers im looking for

 

right now the only way i know how to do this is to

• query = 'select * from answers where answer_id='1''
  
• fetch the row and then another query: select * from answers where user_id = $row['user_id'] and answer_id = '2'
  
• repeat the process with answer_id = '3' /4/5/6/
  

to me this seems inefficent and i assume can be done in 1 query, im just dont know how...

[xtopolis]

SELECT `user_id` FROM `yourTable` WHERE `answer_id` IN('3','4','5','6');

[mapleleaf]

So some clarification

Are you trying to return all the user_ids that put one of any of the four answers in the answer_id?

Or should there be 4 answer columns?

If it is the first change your query to OR instead of AND.

 

[c0rnf3d]

i am trying to return all user_ids that have answered ALL 4 of the answers.

 

[xtopolis]

nm ~

[c0rnf3d]

anyone else got any thoughts?

[fenway]

anyone else got any thoughts?

What was wrong with the supplied code?