Select record from table1 that matches 2 records in table2

Question

I have the following situation.

Table1

+----+-------------+
| id | name        |
+----+-------------+
|  1 | John        |
|  2 | Alice       |
+----+-------------+

Table2

+----+--------------+--------------+
| id | trait        | color        |
+----+--------------+--------------+
|  1 | hair         | blond        |
|  1 | eyes         | blue         |
|  2 | hair         | brown        |
|  2 | eyes         | blue         |
+----+--------------+--------------+

I would like to find the names of everyone who is both blond and blue-eyed in one query. I have tried something like this, but it hasn't worked:

SELECT t1.name FROM table1 as t1, table2 as t2
    WHERE
        t1.id = t2.id AND
        (t2.trait = 'hair' and t2.trait = 'blond') AND
        (t2.trait = 'eyes' and t2.trait = 'blue');

I'm of course looking for the most efficient MySQL way to do this.

Edited: Initial question was too simple and did not accurately reflect my situation

Progman · Answer 1 · 2018-08-19T18:07:06.303

First you build a JOIN which combines the persons with the traits.

SELECT
    p.id,
    p.name,
    t.trait,
    t.color
FROM
    person p
JOIN
    trait t ON p.id = t.userId;
+----+-------+-------+-------+
| id | name  | trait | color |
+----+-------+-------+-------+
|  1 | John  | hair  | blond |
|  1 | John  | eyes  | blue  |
|  2 | Alice | hair  | brown |
|  2 | Alice | eyes  | blue  |
+----+-------+-------+-------+

The you filter it by the traits you are looking for:

SELECT
    p.id,
    p.name,
    t.trait,
    t.color
FROM
    person 
JOIN
    trait t ON p.id = t.userId
WHERE
    (t.trait = 'hair' AND t.color = 'blond') OR
    (t.trait = 'eyes' AND t.color = 'blue');
+----+-------+-------+-------+
| id | name  | trait | color |
+----+-------+-------+-------+
|  1 | John  | hair  | blond |
|  1 | John  | eyes  | blue  |
|  2 | Alice | eyes  | blue  |
+----+-------+-------+-------+

Then you group them by the person to count how many traits a person have from the traits you are looking for:

SELECT
    p.id,
    p.name,
    COUNT(*) AS numTraits
FROM
    person p
JOIN
    trait t ON p.id = t.userId
WHERE
    (t.trait = 'hair' AND t.color = 'blond') OR
    (t.trait = 'eyes' AND t.color = 'blue')
GROUP
    BY p.id, p.name;
+----+-------+-----------+
| id | name  | numTraits |
+----+-------+-----------+
|  1 | John  |         2 |
|  2 | Alice |         1 |
+----+-------+-----------+

And then you pick only the persons which have the required number of traits you are looking for:

SELECT
    p.id,
    p.name,
    COUNT(*) AS numTraits
FROM
    person p
JOIN
    trait t ON p.id = t.userId
WHERE
    (t.trait = 'hair' AND t.color = 'blond') OR
    (t.trait = 'eyes' AND t.color = 'blue')
GROUP BY
    p.id, p.name
HAVING
    numTraits = 2;
+----+------+-----------+
| id | name | numTraits |
+----+------+-----------+
|  1 | John |         2 |
+----+------+-----------+

The `GROUP BY` queries are not valid ANSI GROUP BY SQL and will fail with sql_mode `ONLY_FULL_GROUP_BY`.. But it is ok when MySQL can use functional dependency when `p.id` is a `PRIMARY KEY` or `UNIQUE KEY` the more recent MySQL versions has that feature. — Raymond Nijland, Aug 19 '18 at 17:50
This is a good answer, but unfortunately what it mainly showed was the drawbacks of my question. When I was simplifying the table structure for the question, I simplified it too much in relation to my real use-case. I have updated the question, and would appreciate if you took another look. — Bintz, Aug 19 '18 at 17:54
Actually, nix that! Your answer put me on the right path. I just ended up replacing `t.trait IN ('blond', 'blue-eyed')` with `(t.trait = 'hair' and t.color = 'blond') or (t.trait = 'eyes' and t.color='blue')`. Muchas gracias for your help! — Bintz, Aug 19 '18 at 18:00
@RaymondNijland I have edited the queries to use the columns from the `SELECT` part. Thank you for the information. — Progman, Aug 19 '18 at 18:11
"I just ended up replacing t.trait IN " @Bintz Also MySQL has a nice trick to rewrite `(t.trait = 'hair' AND t.color = 'blond') OR (t.trait = 'eyes' AND t.color = 'blue')` instead off that you can write `(trait, color) IN (('hair', 'blond'), ('eyes', 'blue'))` see demo http://sqlfiddle.com/#!9/d6efbd/3 — Raymond Nijland, Aug 19 '18 at 19:04

score 1 · Accepted Answer · answered Aug 19 '18 at 18:08

1

I think you need to join twice in Table2, one is for hair, another one for eyes.

SELECT
   t1.id,t1.name
FROM
    Table1 t1 
    JOIN Table2 hair ON t1.id = hair.id and hair.trait = 'hair'
    JOIN Table2 eyes ON t1.id = eyes.id and eyes.trait = 'eyes'
WHERE
    (hair.color = 'blond')
AND
    (eyes.color = 'blue')

sqlfiddle

[Results]:

| id | name |
|----|------|
|  1 | John |

answered Aug 19 '18 at 18:08

D-Shih

44,943
6
31
51

Best answer so far. Produces the required result, and is blazing fast in my benchmarking. – Bintz Aug 19 '18 at 18:14

Select record from table1 that matches 2 records in table2

2 Answers2