MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

Question

I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join.

Versioning:

Using MariaDB version 10.3.34

Example Data:

Core (LEFT) data towns

id	town	postcode
1	Hudderfield	HD11 4ER
2	Manchester	MN14 3JE
3	Macklesfield	MK17 9FL
4	Edinburgh	ED5 3MJ
5	Liverpool	LV9 8XT

Joined (RIGHT) data peoples:

id	names	postcode
1	Jimmy Saville	HD11 4ER
2	Jason Bomb	IP14 8FK
3	Micky Mouse	MK17 9FL
4	Bobby Dillian	ED5 3MJ
5	Lenny Davies	ED5 3MJ

My SQL:

My initial query would be something like:

SELECT towns.id, towns.town, peoples.name FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode

But this will include Edinburgh but there are two people in Edinburgh, I only want to join when there's a single unqiue row to join on.

I use LEFT join because I need to return all of towns but only the unique rows of peoples.

Expected results:

id	town	names
1	Hudderfield	Jimmy Saville
2	Manchester	<null>
3	Macklesfield	Micky Mouse
4	Edinburgh	<null>
5	Liverpool	<null>

What I've tried

I've tried using COUNT() in the JOIN but can't get this to work,

SELECT towns.id, towns.town, peoples.names FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode AND count(peoples.id) = 1 

Comes up with a syntax error.

I can't think about how I can qualify this join that it only joins when there's a single result found. Internet searching gives me lots of far more vague and off topic references.

I'm sure it's simple but I can't do it. Also, I'd like to avoid subquerying if possible?

---

Database Fiddle

Answer 1

An alternative way to Lennart's window function answer, is to just use a GROUP BY and HAVING clause against the peoples table to filter out the ones with the same postcode like so:

SELECT towns.id, towns.town, peoples.names
FROM towns
LEFT JOIN
(
    SELECT MAX(names) AS names, postcode
    FROM peoples
    GROUP BY postcode
    HAVING COUNT(name) = 1
) peoples
ON towns.postcode = peoples.postcode

Answer 2

You should be able to left join peoples with:

(select name, postcode
from (
  select name, postcode, count(1) over (partition by postcode) as cnt
  from peoples
) as t
where cnt = 1)

i.e.

SELECT t.id, t.town, p.name 
FROM towns t
LEFT JOIN (SELECT name, postcode
           FROM (
               SELECT name, postcode
                    , count(1) over (partition by postcode) as cnt
               FROM peoples
           ) as x
           WHERE cnt = 1
) p
    USING (postcode)

EDIT:

Given the ddl provided in the update I created the db<>fiddle

SELECT t.id, t.town, p.names 
FROM towns t
LEFT JOIN (
    SELECT names, postcode
    FROM (
       SELECT names, postcode
            , count(1) over (partition by postcode) as cnt
       FROM peoples
    ) as x
    WHERE cnt = 1
) p
    USING (postcode);

It appears to give the expected result

Answer 3

Even simpler:

SELECT t.id, t.town, 
       IF (COUNT(DISTINCT p.names) = 1, MAX(p.names), NULL) AS names
    FROM towns AS t
    LEFT JOIN peoples AS p  ON t.postcode = p.postcode 
    GROUP BY t.id

I believe it avoids the "only full group by" issue mentioned in previous Comments. (If not, see the comments on this Answer.)

Answer 4

Look, mum, no subqueries and aggregate or windowing functions!

SELECT t.id
     , t.town
     , IF( p2.id IS NOT NULL, NULL, p1.names ) AS names
  FROM towns AS t
  LEFT JOIN peoples AS p1  ON p1.postcode =  t.postcode
  LEFT JOIN peoples AS p2  ON p2.postcode = p1.postcode
                          AND p2.id != p1.id
 GROUP BY t.id
;