| Author |
 Topic  |
|
|
SergioM
Yak Posting Veteran
68 Posts |
 Posted - 08/25/2010 : 10:20:36
|
I have the following SQL query. Most of my data is in "Inventory" but there is one thing that I want in "Suppliers". I've used the SQL Server query editor to make this & it added "Suppliers" with a CROSS JOIN command.
It's supposed to output about 30,000 rows. However it outputs around 240,000 rows. Rather than identifying the supplier, it duplicates the item for every supplier I have!
I've taken out the CROSS JOIN command and then it does work normally. However when I put it back, I (again) get 8X the data. Does anyone know how to correct this? For your reference, the full SQL Query is below.
SELECT Company, Items.Title, Items.Manufacturer, Items.Part_No, Inventory.QtyOnHand, Inventory.Cost
FROM Inventory INNER JOIN
Items ON Inventory.InventoryID = Items.InventoryID CROSS JOIN Suppliers
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/25/2010 : 10:43:15
|
Instead of CROSS JOIN the Suppliers table (basicallt a cartesian product), you should INNER JOIN the Suppliers table to the Items table over SupplierID column.SELECT Suppliers.Company,
Items.Title,
Items.Manufacturer,
Items.Part_No,
Inventory.QtyOnHand,
Inventory.Cost
FROM Inventory
INNER JOIN Items ON Items.InventoryID = Inventory.InventoryID
INNER JOIN Suppliers ON Suppliers.SupplierID = Items.SupplierID
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/25/2010 : 10:49:49
|
Or, it Inventory holds the Supplier informationSELECT Suppliers.Company,
Items.Title,
Items.Manufacturer,
Items.Part_No,
Inventory.QtyOnHand,
Inventory.Cost
FROM Inventory
INNER JOIN Items ON Items.InventoryID = Inventory.InventoryID
INNER JOIN Suppliers ON Suppliers.SupplierID = Inventory.SupplierID
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SergioM
Yak Posting Veteran
68 Posts |
Posted - 08/25/2010 : 13:42:20
|
Thank you. However if I replace CROSS JOIN with INNER JOIN, OUTER JOIN, LEFT JOIN or RIGHT JOIN, I get the following error message.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'WHERE'.
I forgot to mention that my Query does have a WHERE command in the next line.
WHERE Inventory.QtyOnHand > 0
The only thing that does seem to work is CROSS JOIN, but it provides results that I can't use |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 08/25/2010 : 13:53:07
|
Please show us the query that gives the posted error message.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 08/25/2010 : 14:04:08
|
when you replace the CROSS JOIN do not forget the
ON Items.InventoryID = Inventory.InventoryID or whatever fields you are joining on as Peso specified
If you don't have the passion to help people, you have no passion |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/25/2010 : 14:16:35
|
WHERE clause is placed LAST.SELECT Suppliers.Company,
Items.Title,
Items.Manufacturer,
Items.Part_No,
Inventory.QtyOnHand,
Inventory.Cost
FROM Inventory
INNER JOIN Items ON Items.InventoryID = Inventory.InventoryID
INNER JOIN Suppliers ON Suppliers.SupplierID = Inventory.SupplierID
WHERE Inventory.QtyOnHand > 0
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SergioM
Yak Posting Veteran
68 Posts |
Posted - 08/27/2010 : 21:45:38
|
@ webFred - The error was a result of using INNER JOIN without binding it (IE "ON Inventory.InventoryID = Items.InventoryID").
@ yosiasz - Someone posted (but deleted their response?), but it seems to be true. CROSS JOIN does not seem to support the Binding rule.
@ Peso - Yep, WHERE does appear last in my query.
If I use INNER JOIN & bind it with "ON Inventory.InventoryID = Items.InventoryID", it does work; but I get 8X the data - one for each supplier
If I use CROSS JOIN, I get 8x the data - one for each supplier.
Again, my query looks like this.
SELECT Company, Items.Title, Items.Manufacturer, Items.Part_No, Inventory.QtyOnHand, Inventory.Cost
FROM Inventory INNER JOIN
Items ON Inventory.InventoryID = Items.InventoryID
CROSS JOIN Suppliers
WHERE Inventory.QtyOnHand > 0
Thoughts, ideas, opinions? |
Edited by - SergioM on 08/27/2010 21:46:06 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/28/2010 : 02:30:43
|
Yes. Stop using CROSS JOIN Suppliers!
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/28/2010 : 04:10:08
|
quote:
Originally posted by SergioM
If I use INNER JOIN & bind it with "ON Inventory.InventoryID = Items.InventoryID", it does work; but I get 8X the data - one for each supplier
If you us INNER JOIN, as you should, you must also bind THAT table (Suppliers) accordingly.
If you INNER JOIN Suppliers table, but bind two others, you get CROSS JOIN. AGAIN!
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
SQL Query results in *8X* the data?
