snippetMinor
MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists
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
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
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:
But this will include
I use LEFT join because I need to return all of
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
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
Versioning:
Using MariaDB version 10.3.34
Example Data:
Core (LEFT) data
townsid
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.postcodeBut 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) = 1Comes 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.postcodeCode 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.postcodeContext
StackExchange Database Administrators Q#312757, answer score: 6
Revisions (0)
No revisions yet.