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



  • 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



  • 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2