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

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

---

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'

---

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.

---

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

---

The HAVING clause is the key. It turns the query from an "OR" statement into an "AND" statement