7

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

3
  • What do you want when there are 2 matching rows on the 'right'?
    – Rick James
    May 31 at 16:44
  • Not relevant to the technical question, but assuming this is real data (not make believe data created as an example to ask about the concept): in your data those are street (or part-street) level postcodes. You could have many people in the same town with different postcodes, all but the smallest village will have more than one postcode of that length, a town or city will have many. In the past I've lived at YO1 5BQ and YO10 4DL, both in York. May 31 at 17:05
  • @DavidSpillett yes this is simply example data for a real issue combining records based on postcode but disallowing multiple records with duplicate postcodes. Also I don't think Jimmy Saville was actually from Huddersfield.
    – Martin
    May 31 at 20:50

4 Answers 4

6

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
7
  • What's the purpose of MAX here?
    – Martin
    May 31 at 12:01
  • 1
    When you use a GROUP BY clause, you have to use an aggregate function to select columns that aren't being grouped on. In this case, since you only care about the groups that have 1 value in them (i.e. the people with unique post codes) then it doesn't matter which aggregate function you use here, MAX or MIN, it'll always be the same 1 person.
    – J.D.
    May 31 at 12:04
  • 1
    @Martin Yea that's true, in some versions of MySQL and MariaDB, you're technically not required to use an aggregate function on the non-grouped fields, which then I believe it picks a value at random (in this case again it wouldn't matter since each grouping only has 1 row / person in it anyway, so same outcome either way). I'm used to Microsoft SQL Server, where it is required to use an aggregate function.
    – J.D.
    May 31 at 14:35
  • 2
    In MySQLit's controlled by the ONLY_FULL_GROUP_BY SQL mode. This is off by default pre-5.7, on by default since then. @SalmanA It should be required by any sane RDBM unless names is functionally dependent on postcode, which it isn't here. While it's true that it's redundant when you specifically restrict to count=1, I don't think the database is required to detect this special case.
    – Barmar
    Jun 1 at 14:33
  • 1
    My bad. I now see that names is being used in outermost select so yes, we need people.names which in turn needs to be placed inside max or some other aggregate fn.
    – Salman A
    Jun 1 at 16:11
5

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

5
  • Note: If you want to try the query in your fiddle, you need to change version of MySQL to 8.
    – Lennart
    May 31 at 11:53
  • You can avoid a level of nested subqueries by using HAVING instead of WHERE.
    – Barmar
    Jun 1 at 14:35
  • Do you mean using a window function in a HAVING clause?
    – Lennart
    Jun 1 at 15:57
  • I mean like J.D.'s answer.
    – Barmar
    Jun 1 at 15:59
  • Yes, then I agree
    – Lennart
    Jun 1 at 16:02
5

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.)

10
  • 1
    Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.p.names' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    – jcaron
    Jun 1 at 17:17
  • 1
    However adding a GROUP_CONCAT or MAX on p.names will resolve it.
    – jcaron
    Jun 1 at 17:18
  • @jcaron simply adding the p.names field to the group by fixed this for me, I think.
    – Martin
    Jun 1 at 20:33
  • @Martin - I changed the SQL, check it.
    – Rick James
    Jun 1 at 21:00
  • @Martin it fixes the error but shouldn’t give the result you want (and wouldn’t make much sense since you would have an aggregate function on a field you are grouping by).
    – jcaron
    Jun 1 at 21:03
2

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
;
1
  • gold star for that!!
    – Martin
    Jun 2 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.