filter duplicates in SQL join

Question

When using a SQL join, is it possible to keep only rows that have a single row for the left table?

For example:

select * from A, B where A.id = B.a_id;

a1 b1
a2 b1
a2 b2

In this case, I want to remove all except the first row, where a single row from A matched exactly 1 row from B.

I'm using MySQL.

Answer 1

This should work in MySQL:

select * from A, B where A.id = B.a_id GROUP BY A.id HAVING COUNT(*) = 1;

For those of you not using MySQL, you will need to use aggregate functions (like min() or max()) on all the columns (except A.id) so your database engine doesn't complain.