SQL select with multiple conditions on the same table

Question

I have 2 tables in MySQL, the first one has 2 columns: ID and name, the second has 3 columns: firstTableId (foreign key on the first table), key, value.

I have the following rows in table 1:

• 1,Bob
• 2,Alice
• 3,Fred

I have the following rows in table 2:

• 1,age,20
• 1,gender,male
• 2,age,20
• 2,gender,female
• 3,age,18
• 3,gender,male

I would like to write a select query using only the last 2 columns on the second table (key and value) that returns only Bob form the first table, but I can't seem to figure it out.

Essentially I want to select from the first table all rows where, in the second table, we have key=age and value=20 for one row, and key=gender and value=male in another row. Can anyone point me in the right direction ? Manipulating table structure is not preferred as this is a simplified example and both "key" and "value" columns in the second table can be pretty much anything, it's not actually limited to "age" and "gender".

Thanks in advance.

Answer 1

You can do this with a self join like this:

select
  *
from
  table1 t1
  inner join table2 age on t1.id = age.id
  inner join table2 gender on t1.id = gender.id
where
  (age.`key` = 'age' and age.value = 20)
  and 
  (gender.`key` = 'gender' and gender.value = 'male')

An additional tactic you may want to try is a PIVOT query. Mysql doesnt have anything native to support pivot's, but there are several examples of how to do them.

You can see it working in this fiddle

Answer 2

Use two IN clauses (or two EXISTS clauses).

select *
from table1
where id in (select firstTableId from table2 where key = 'age' and value = '20')
and id in (select firstTableId from table2 where key = 'gender' and value = 'male');

With EXISTS:

select *
from table1
where exists (select * from table2 where key = 'age' and value = '20' and firstTableId = table1.firstTableId)
and exists (select * from table2 where key = 'gender' and value = 'male' and firstTableId = table1.firstTableId);