CODEWITHSUNDEEP
mysqlsql-serverleft-join
atoms
atoms

Reputation: 3093

LEFT JOIN tables and null values that dont exist in the right

I have two tables, Customers and customerTowns. I would like to store the town name in customerTowns and link to it from townID stored in Customers

|Customers| |townID |

|customerTowns| |townID | |townName |

And my sql is as followed,

SELECT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID

This nearly returns the result im looking for. Except, I would like the townID to be repeated for each entry in customerTowns.

|townName |townID| |London |1 | |London |1 | |London |1 | |London |1 | |London |1 | |Manchester|NULL | |Liverpool |NULL |

I feel I must be close, just cant figure out how to return just one row, or why its returning multiple rows!

My desired output would be;

|townName |townID| |London |1 | |Manchester|NULL | |Liverpool |NULL |

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Probably the most efficient way to do this is using exists:

SELECT ct.townName, 
       (exists (select 1 from Customers c where c.townID = ct.townId))
FROM customerTowns ct;

The reason your original query is returning duplicates is because it returns one row for each customer. Because your join is on TownId in both tables, you really just need to see if a customer exists for that town. This should perform better than a query that requires a distinct or group by.

Upvotes: 1

wvdz
wvdz

Reputation: 16641

Either use a group by or distinct.

SELECT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID
GROUP BY townName, Customers.townID;

SELECT DISTINCT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID;

Upvotes: 2

Related Questions