HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
leftexistssidejoinensurerecordsinglehowonlymariadb

Problem

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?

Database Fiddle

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#312757, answer score: 6

Revisions (0)

No revisions yet.