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.
Using MariaDB version 10.3.34
Core (LEFT) data
Joined (RIGHT) data
|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 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
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?