如何获取表中的行？

Question

I have a table with the following columns:

User_id, Key, Value

In this table are records such as:

1, "first name", "Bob"  
2, "first name", "Mike"  
1, "last name", "Jones"  
2, "last name", "Carter"

I want to get the user_id for a specific first and last name pair. How can I get a query to do this? For example, I want to get 1 if I query for Bob Jones

Answer 1

Join the table to itself:

SELECT t1.User_id
FROM myTable t1
JOIN myTable t2 ON t1.User_Id = t2.UserId
WHERE t1.Key = 'first name' AND t2.Key = 'last name'
AND t1.Value = "Bob" AND t2.Value = "Jones"

Or with following concatenated condition:

SELECT t1.User_id
FROM myTable t1
JOIN myTable t2 ON t1.User_Id = t2.UserId
WHERE t1.Key = 'first name' AND t2.Key = 'last name'
AND (t1.Value + ' ' + t2.Value) = "Bob Jones"