Hello everyone

I have two tables in my application:

table_X
+-------+
| a_id | b_id |
+-------+
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |

table_Z
+-----------------+
| b_id | system_flag |
+----------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |

In this two tables all table_X records have one or more specific b_id's from another table (table_Z). b_id's in table_Z could be system or not system. What I need is a query that would list all a_id's that does not have any b_id's with system flag. (If a_id has even one b_id that is sytem, it should be not returned).

How can I achieve this ?

aus, that's pretty cool

it also works with LEFT OUTER JOIN, in case there's a row in table_X which has no rows in table_Z -- since SUM() ignores nulls, it will work too!

neat, eh?

Good point, I do tend to forget about some records not showing up in the result set. I guess that if the tables were created without referential integrity enforced, the LEFT JOIN would be required.

SELECT a_id FROM table_X
JOIN table_Y USING(b_id)
GROUP BY a_id HAVING SUM(system_flag)=0;

Bingo!

Thank you very much