SQL inner join creates duplicates

Question

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:

Answer 1

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.

Answer 2

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).