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
– okaram, Commented Oct 23, 2014 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
– Thorsten Kettner, Commented Oct 23, 2014 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.
– Pierre P., Commented Oct 23, 2014 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
– Thorsten Kettner, Commented Oct 24, 2014 at 6: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.
– Pierre P., Commented Nov 5, 2014 at 2:06

Community · Accepted Answer · 2017-05-23 12:33:32Z

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, 2017 at 12:33

CommunityBot

11 silver badge

answered Oct 23, 2014 at 21:29

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pierre P. Over a year ago

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.

Thorsten Kettner · Accepted Answer · 2014-10-23 21:50:08Z

4

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

answered Oct 23, 2014 at 21:50

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

SQL select with multiple conditions on the same table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related