Which JOIN type in multiple joins

Question

I have 4 tables that I want to be joined.

Customers
Traffic
Average
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?

All the data in 4 of the tables are unique. distinct does not have any changes. — Bayan B, Jun 08 '15 at 10:45
@BayanB . . . You say: "But the result will have duplicated rows.". Are there or are there not duplicates? Edit your question with sample data and desired results. Better yet, use SQL Fiddle. — Gordon Linoff, Jun 08 '15 at 11:27
Note that an outer join usually returns same number of rows or more than an inner join! — jarlh, Jun 08 '15 at 11:57

score 0 · Answer 1 · answered Jun 08 '15 at 10:44

Can you please try this

        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'
    GROUP BY
        cid,
        Customer_Name,
        Traffic.Router_Name,
        Traffic.Received, 
        Average.Received,
        Live.Received, 
        Traffic.date
    )

Why not save a lot of typing and do SELECT DISTINCT instead? — jarlh, Jun 08 '15 at 11:56

score 0 · Answer 2 · answered Jun 08 '15 at 10:51

0

SELECT Customers.id AS cid, Customers.name AS Customer_Name, Traffic.Router_Name,
Traffic.Received, 
Average.Received,
Live.Received, 
Traffic.date
FROM Customers
LEFT JOIN Traffic ON Customers.id=Traffic.cid
LEFT JOIN Average ON Traffic.cid=Average.cid
LEFT JOIN Live ON Average.cid=Live.cid
WHERE Traffic.date='2015-06-08'

answered Jun 08 '15 at 10:51

CathalMF

9,705
6
70
106

There is no difference in the result – Bayan B Jun 08 '15 at 10:53
In that case your tables must have more than one entry for each customer on this date. Pick a customer and see what data they have in each table for your date. – CathalMF Jun 08 '15 at 10:55
The Details tables saves these data every day, and for each customer there is only one entry every day. For example for 2015-06-08 each of the 3 tables (Traffic, Live and Average) have one record for each customer. – Bayan B Jun 08 '15 at 10:59

score 0 · Accepted Answer · answered Jun 08 '15 at 12:52

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.

Which JOIN type in multiple joins

3 Answers3