How to left join when only matched one record from right table

user_15095135 0
Viewed 34

I want to know How to left join when only matched one record from right table.
For example,

tableA

id value
1 34
2 42
3 60

tableB

id value tableA_id
1 20 1
2 31 1
3 50 2

I want to get result like below using left outer join.

tableA_id tableA_value tableB_value
1 34 null
2 42 50
3 60 null

tableB_value of first row is null, because tableA.id = tableB.tableA_id matches multiple records.

how to solve it ? Thanks.

mysql sql
2 Answers

You can make use of COUNT() as an analytic function to keep track of how many times a tableA_id occurs in the A table:

SELECT a.id AS tableA_id, a.value AS tableA_value, b.value AS tableB_value
FROM tableA a
LEFT JOIN
(
    SELECT *, COUNT(*) OVER (PARTITION BY tableA_id) cnt
    FROM tableB
) b
    ON a.id = b.tableA_id AND b.cnt = 1
ORDER BY a.id;

screen capture from demo link below

Demo

user_18632290

You can do this using aggregation on the b table as well:

SELECT a.id AS tableA_id, a.value AS tableA_value, b.value AS tableB_value
FROM tableA a LEFT JOIN
     (SELECT tableA_id, MAX(value) as value
      FROM tableB
      GROUP BY tableA_id
      HAVING COUNT(*) = 1
     ) b
     ON a.id = b.tableA_id
ORDER BY a.id;

This works because if there is only one row in B for a given id, then MAX() returns the value on that row.

user_11440350
Related