CODEWITHSUNDEEP
sqlselect
Danny Hickerz
Danny Hickerz

Reputation: 109

SQL Query Showing 4x Records

The following statement works properly but shows each record 4 times. Repeated; I know the relationship is wrong but no idea how to fix it? Apologies if this is simple and i've missed it.

SELECT Customers.First_Name, Customers.Last_Name, Plants.Common_Name, Plants.Flower_Colour, Plants.Flowering_Season, Staff.First_Name, Staff.Last_Name
FROM Customers, Plants, Orders, Staff
INNER JOIN Orders AS t2 ON t2.Order_ID = Staff.Order_ID
WHERE Orders.Order_Date
BETWEEN  '2011/01/01'
AND  '2013/03/01'

Upvotes: 1

Views: 106

Answers (3)

Taryn
Taryn

Reputation: 247810

You are generating a Cartesian product between the tables since you have not provided join syntax between any of the tables:

SELECT c.First_Name, c.Last_Name, 
    p.Common_Name, p.Flower_Colour, p.Flowering_Season, 
    s.First_Name, s.Last_Name
FROM Customers c
INNER JOIN Orders o
    on c.customerId = o.customer_id 
INNER JOIN  Plants p
    on o.plant_id = p.plant_id
INNER JOIN Staff s
    ON o.Order_ID = s.Order_ID
WHERE o.Order_Date BETWEEN  '2011/01/01' AND  '2013/03/01'

Note: I am guessing on column names for the joins

Here is a great visual explanation of joins that can help in learning the correct syntax

Upvotes: 6

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

This is because you are selecting from four tables without any joins between them, and also because you are joining Orders twice. As the result, a Cartesian product is made.

Here is how you should fix it: re-write the theta join using the ANSI syntax, and provide proper join conditions:

SELECT Customers.First_Name, Customers.Last_Name, Plants.Common_Name, Plants.Flower_Colour, Plants.Flowering_Season, Staff.First_Name, Staff.Last_Name
FROM Customers
JOIN Plants ON ...
JOIN Orders ON ...
JOIN Staff ON ...
INNER JOIN Orders AS t2 ON t2.Order_ID = Staff.Order_ID
WHERE Orders.Order_Date BETWEEN  '2011/01/01' AND  '2013/03/01'

Replace ... with proper join conditions; this should make the results look as expected.

Upvotes: 2

Anders Abel
Anders Abel

Reputation: 69270

In the FROM... clause you are doing a cross join - combining every customer with every plant with every order with every staff.

You should only mention one table in the FROM clause and then connect the other ones with INNER JOINS to only get related records.

I don't know exactly how your database looks like, but something like this:

SELECT Customers.First_Name, Customers.Last_Name, Plants.Common_Name,
Plants.Flower_Colour, Plants.Flowering_Season, Staff.First_Name, Staff.Last_Name
FROM Customers
INNER JOIN Orders ON Orders.Customer_ID = Customers.Customer_ID
INNER JOIN Staff ON Staff.Staff_ID = Orders.Staff_ID
INNER JOIN Plants ON Plants.Plants_ID = Orders.Plants_ID
WHERE Orders.Order_Date
BETWEEN  '2011/01/01'
AND  '2013/03/01'

Upvotes: 2

Related Questions