MySQL - Matching 2 rows (or more!) within a sub-query/table

Question

I have this schema which I need to match 2 rows from

user_data : user_id, field_id, value

A sample output would be:

user_id     field_id    value
-----------------------------
  1         1           Gandalf
  1         2           Glamdring

How do I write a query which basically say "Find the user id of the user whose field_id 1 is Gandalf, and field_id 2 is Glamdring?"

SELECT FROM looks at one row at a time. I am stumped on this. I will also need to find a solution that scale gracefully (such as looking at three rows etc.)

Answer 1

I would try the following:

SELECT user_id
FROM user_data
WHERE ( field_id = 1 AND value= 'Gandalf' )
   OR ( field_id = 3 AND value = 'Glamdring' )
GROUP BY user_id
HAVING COUNT( field_id ) = 2

It will search for all the rows that match one of your criteria, and use GROUP BY and HAVING afterwards to find the user_id that has the expected count of matches.

Answer 2

select * from user_date where ( field_id= 1 AND value='Gandalf' ) OR ( field_id =2 AND value ='Glamdring' ) ;

Answer 3

You could run a query to get the users that match each of the conditions and intersect the results. Since MySQL doesn't support intersect you can do it with an n-way join:

SELECT T1.user_id
FROM Table1 T1
JOIN Table1 T2 ON T1.user_id = T2.user_id
WHERE T1.field_id = 1 AND T1.value = 'Gandalf'
AND T2.field_id = 2 AND T2.value = 'Glamdring'