CODEWITHSUNDEEP
sqlinner-join
ogward
ogward

Reputation: 1193

SQL inner join creates duplicates

I have three tables, Suppliers, Products and Deliveries. I'd like to show the suppliers names and the quantity from the delivery table. There are 5 different suppliers and there are 12 deliveries. I expect that the joined table should contain 12 rows with names and deliveries.

This is my code. 

SELECT  Suppliers.SNAME, Deliveries.QTY
FROM Suppliers, Deliveries
INNER JOIN  Products
ON Deliveries.P=Products.Penter (typo, should be Products.P)

The output is 60 rows with many duplicates and some incorrect matches.

Tables: Suppliers Products Deliveries

Upvotes: 5

Views: 28744

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Kill off deprecated implicit joins, and specify JOIN criteria, not sure of your table structures, something like:

SELECT  s.SNAME, d.QTY
FROM Deliveries d
INNER JOIN Suppliers s
 ON d.s = s.s
INNER JOIN  Products p
 ON d.p = p.p

An unspecified implicit JOIN ie:

SELECT  Suppliers.SNAME, Deliveries.QTY
FROM Suppliers, Deliveries

Will result in every record from each table joining to every record in the other, in your case, 5 records and 12 records = 60 combinations.

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use proper join syntax consistently. You are mixing old-style joins (, with condition in the where clause) with proper ANSI join syntax.

You mean something like this:

SELECT s.SNAME, d.QTY
FROM Suppliers s join
     Deliveries d
     d.SupplierId = s.SupplierId join
     Products p
     ON d.P = Products.Penter;

I'm making up the join field between Suppliers and Deliveries. And, I'm only guessing that those are the tables that need to be connected (perhaps it is Suppiers and Products).

Upvotes: 3

Related Questions