Duplicate filter in SQL join

Question

When using an SQL join, is it possible to save only single row rows for the left table?

For instance:

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

a1 b1
a2 b1
a2 b2

In this case, I want to delete everything except the first line, where one line from A matches exactly 1 line from B.

I am 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 who are not using MySQL, you will need to use aggregate functions (e.g. min () or max ()) for all columns (except A.id) so that your database engine does not complain.

Answer 2

, . , B ( , ).

, , - B.

SELECT *
FROM A, B
WHERE B.id =
 (SELECT MIN(B.id)
  FROM B
  WHERE A.id = B.a_id);

Answer 3

-, JOIN . -, A.id A, B :

Select ...
From A
    Join B
        On B.a_id = A.id
Where Exists    (
                Select 1
                From B B2
                Where B2.a_id = A.id
                Having Count(*) = 1
                )

Answer 4

, .

, / .

select
  *
from
  A
  join B on A.id = B.a_id
where
  not exists (
    select
      1
    from
      B B2
    where 
      A.id = b2.a_id 
      and b2.id != b.id
  )