Search code examples
mysqlsqljoinleft-joinright-join

Which JOIN type in multiple joins

I have 4 tables that I want to be joined.

  1. Customers
  2. Traffic
  3. Average
  4. Live

I want to insert joined data of these tables to "Details" table. The relationship between the tables is here: each of Traffic, Average and Live tables have a "cid" that is the primary key of "Customers" table:

  • Traffic.cid = Customers.id
  • Average.cid = Customers.id
  • Live.cid = Customers.id

The query that I wrote is here:

INSERT INTO Details
(
cid, Customer_Name, Router_Name,
Traffic_Received,
Average_Received,
Live_Received,
date
)     
(
SELECT Customers.id AS cid, Customers.name AS Customer_Name, Traffic.Router_Name,
Traffic.Received, 
Average.Received,
Live.Received, 
Traffic.date
FROM Customers
INNER JOIN Traffic ON Customers.id=Traffic.cid
INNER JOIN Average ON Customers.id=Average.cid
INNER JOIN Live ON Customers.id=Live.cid
WHERE Traffic.date='2015-06-08'
)

But the result will have duplicated rows. I changed the JOIN to both LEFT JOIN, and RIGHT JOIN. but the result does not changed. What should I do to not have duplicated rows in Details table?

Solution

  • With the LEFT JOIN, you will be joining to the table (e.g. Traffic) even when there is not a record that corresponds to the Customers.id, in which case, you will get the null value for the columns from this table where there is no matching record.

    With the RIGHT JOIN, you will get every record from the joined table, even when there is not a corresponding record in Customers.

    However, the type of JOIN is not the problem here. If you are getting duplicate records in your results, then this means that is more than one matching record in the tables you are joining to. For example, there may be more than one record in Traffic with the same cid. Use SELECT DISTINCT to remove duplicates, or if you are interested in an aggregate of those duplicates, use an aggregate function, such as count() or sum() and a GROUP BY clause, e.g. GROUP BY Traffic.cid.

    If you still have duplicates, then check to make sure that they really are duplicates - I'd suggest that one or more columns is actually different.