SQL SELECT: restrict rows from Table1 to those having only specific rows in Table2
0
votes
1
answer
81
views
(A simplified example).
I have Table1 with items:
+----+-------+
| id | name |
+----+-------+
| 1 | car |
| 2 | apple |
| 3 | grass |
| 4 | alien |
+----+-------+
and Table2 with colors that these items can be:
+----+------+--------+
| id | item | color |
+----+------+--------+
| 1 | 1 | blue |
| 2 | 1 | green |
| 3 | 2 | red |
| 4 | 2 | green |
| 5 | 2 | yellow |
| 6 | 3 | green |
| 7 | 3 | yellow |
| 8 | 4 | yellow |
| 9 | 4 | green |
+----+------+--------+
I need to select all items (and ideally their colors, too) that can be either green or yellow, but not in any other color. I'm stuck at WHERE in SELECT Table1.id, Table1.name, Table2.color FROM (Table1 JOIN Table2 ON Table1.id=Table2.item) WHERE…
. Can you help me?
Ideally the result should be:
+-----------+-------------+---------------+
| Table1.id | Table1.name | Table2.color |
+-----------+-------------+---------------+
| 3 | grass | green |
| 3 | grass | yellow |
| 4 | alien | yellow |
| 4 | alien | green |
+-----------+-------------+---------------+
(Sorry if this is a duplicate question of a already answered one, but I couldn't find one here, I even have no idea what keywords to search 🤷♂️)
Asked by Kaligula
(3 rep)
Aug 29, 2024, 11:20 PM
Last activity: Sep 8, 2024, 07:31 PM
Last activity: Sep 8, 2024, 07:31 PM