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

[emmalee]

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
3	Macklesfield	Micky Mouse
4	Edinburgh
5	Liverpool

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?

---

https://www.db-fiddle.com/f/axkxaR2RVzzdMWNE3dWsfL/0

[charlenef]

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