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

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.

Answer

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;

Demo