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.

You probably want to use IN and subqueries (can do ugly joins, but these tend to be worse) — okaram, Oct 23 '14 at 21:27
This is a horrible database design by the way. Complicated and slow access, no useful indexes, no constraints, no difference between CAN and MUST values... With such generic approach you keep the dbms from helping you. — Thorsten Kettner, Oct 23 '14 at 22:08
Fair enough, but given that both "key" and "value" have to be completely generic (any value for both these columns is possible) and there has to be a one-to-many relationship between table 1 and 2, what would be a preferable design ? — Pierre P., Oct 23 '14 at 22:16
I don't see why it must be possible for these columns to take any value. What would age = 'car' or gender = 'saussage' actually mean? To start with: Can table 1 contain anything else than persons? Otherwise, why doesn't the table simply contain two columns age and gender? (Thus making sure that age is a number and gender "male" or "female" or null for "unknown", and not "mail" by mistake :-) If on the other hand table 1 *can* contain also pets and favorite colors, I find it hard to imagine what the database is for. I agree there are cases when generic tables are okay, but these cases are rare. — Thorsten Kettner, Oct 24 '14 at 06:47
The example above is simplified, in reality it has nothing to do with people, age or gender, it's an unbounded set of different properties (identified by keys) of various datatypes, and I need the ability to query against these properties. What I need is, for each record in table 1, a map of key-value pairs that I can query against. The resulting keyset (i.e. the union of all keysets of all maps for all table 1 records) is unbounded and can grow over time. This data structure fits that requirement. — Pierre P., Nov 05 '14 at 02:06

score 5 · Accepted Answer · edited May 23 '17 at 12:33

5

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

edited May 23 '17 at 12:33

Community

1
1

answered Oct 23 '14 at 21:29

crthompson

15,653
6
58
80

Yep, that works, exactly what I wanted, and since the number of these clauses is relatively limited (up to 3... for now), performance impact should be acceptable, thank you :) Upvoting. – Pierre P. Oct 23 '14 at 21:51

score 4 · Answer 2 · answered Oct 23 '14 at 21:50

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

SQL select with multiple conditions on the same table

2 Answers2