Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Query results in *8X* the data?

Author  Topic 

SergioM
Posting Yak Master

170 Posts
Posted - 2010-08-25 : 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

30421 Posts
Posted - 2010-08-25 : 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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-08-25 : 10:49:49
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

170 Posts
Posted - 2010-08-25 : 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
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-08-25 : 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.
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2010-08-25 : 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-08-25 : 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"
Go to Top of Page

SergioM
Posting Yak Master

170 Posts
Posted - 2010-08-27 : 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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-08-28 : 02:30:43
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

30421 Posts
Posted - 2010-08-28 : 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"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site