3 Table Join giving me duplicate records

I’m trying to do a 3 table join and it’s giving me duplicate records so I’m unsure what I’m doing incorrectly.

SELECT e.*, cs.*, c.* FROM employee e 
LEFT JOIN coffee_shop cs ON e.shop_id = cs.shop_id 
LEFT JOIN coffee c ON cs.shop_id = c.shop_id

I want the coffee_shop table to join on the employee table’s shop_id and the coffee table to join on coffee_shop's shop_id to perform a 3 table join. However it’s giving me duplicate rows (9 rows) when all the tables only have 3 rows each.

How do I perform this join without duplicates?

Edit:

If I do only the join on the first two tables(employee and coffee_shop) it is as expected

I want to perform one more join from coffee onto coffee_shop. Which should also return 3 rows

Here is the result I want:

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This will give you 3 rows for your use-case. For each shop_id in the coffee table, this will return the row with the first coffee_id.

Depending on which coffee you want to return, you can adjust the logic for the row_rank field.

SELECT e.*, cs.*, c.* 
FROM employee e 
LEFT JOIN coffee_shop cs ON e.shop_id = cs.shop_id 
LEFT JOIN (
    SELECT *,
           RANK() OVER(PARTITION BY shop_id ORDER BY coffee_id) as row_rank
    FROM coffee
) c ON cs.shop_id = c.shop_id and c.row_rank = 1

Method 2

Try to use DISTINCT in your query like :

SELECT DISTINCT  e.*, cs.*, c.* FROM employee e 
LEFT JOIN coffee_shop cs ON e.shop_id = cs.shop_id 
LEFT JOIN coffee c ON cs.shop_id = c.shop_id

Method 3

if your main tables is employee,

SELECT e.*, cs.*, c.* FROM employee e 
INNER JOIN coffee_shop cs ON e.shop_id = cs.shop_id 
LEFT JOIN coffee c ON cs.shop_id = c.shop_id

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0