I have two tables like this:
Table1
| Col1 | Col2 |
|---|---|
| A | X |
| B | X |
| C | X |
| A | Y |
| B | Z |
Table2
| Col3 | Col4 |
|---|---|
| X | true |
| Y | false |
| Z | true |
How can I write a SQL query such that I get the Col1 values where that row only has a Col2 join with table 2 which have a col 4 that is true and must not contain a false value.
So for the example tables, the values returned would be B and C. A would not be returned as the join to Table 2 for row Y would be false.
So this query needs to get the unique list of values in Col1, then from that join table1 and table2 on Col2, and then from the unique list, get those which only have a true value on this joined table.
CodePudding user response：
You can join the tables and use aggregation with the condition in the HAVING clause:
SELECT t1.Col1
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.Col3 = t1.Col2
GROUP BY t1.Col1
HAVING MIN(t2.Col4) = true; -- just HAVING MIN(t2.Col4) would also work
If the min value of Col4 for a specific Col1 is true which is interpreted as 1, this means that there is no false which is interpreted as 0.
See the demo.