SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Query results in *8X* the data?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

168 Posts

Posted - 08/25/2010 :  10:20:36  Show Profile  Reply with Quote
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
30282 Posts

Posted - 08/25/2010 :  10:43:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/25/2010 :  10:49:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or, it Inventory holds the Supplier information
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



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 08/25/2010 :  13:42:20  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 08/25/2010 :  13:53:07  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 08/25/2010 :  14:04:08  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/25/2010 :  14:16:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 08/27/2010 :  21:45:38  Show Profile  Reply with Quote
@ 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/28/2010 :  02:30:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. Stop using CROSS JOIN Suppliers!



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/28/2010 :  04:10:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000