3 Table Join giving me duplicate records-CodePudding

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:

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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