Why am I getting 3,000 identical rows??? 1

[MissouriTiger]

I have a SQL query that keeps crashing my script. In Query Analyzer it returns 3152 identical rows (there is only one record in the DB that matches the criteria). In fact, just matching the idCustTemp alone will only return a handful.

What am I doing wrong?

My query is pasted below:

SELECT C.idCartRow, C.ItemDesc, C.quantity, C.unitPrice, P.idProduct, PD.Details
FROM cartRows C, products P, brands B, ProductDescriptions PD
WHERE C.idProduct = P.idProduct
AND PD.idProduct = P.idProduct
AND C.idSite = 7
AND C.Status = 0
AND C.idCustTemp = 11

All ideas/suggestions are welcome!





Greg Norris
Software Developer & all around swell guy

http://www.GregNorris.net

__________________________________________________
Constructed from 100% recycled electrons.

 

[fredericofonseca]

because you are not joining all the tables.

You are joining C, P and PD, and not B, and as such you will get all records of B multiplied by the number of records the others joins give you.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[AlexCuse]

Sounds like you are getting a cartesian product. If you use inner joins in the query that should cut it down.

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[SQLSister]

This is a bad syntax to use just for this reason. What you got was a cross join. Use the standard join syntax instead.
It will help prevent this forgetting to specify what you are joining on problem.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[MissouriTiger]

Wow, I can't believe I got 3 responses in 10 minutes.

I just came back here to say I figured out what the problem was. Yes, I have an extra table listed (result of pasting code). Doh!

You are right about using the explicit join syntax. I need to do that from now on. Old habits die hard, espcially when copying/pasting/modifying old queries, but I will try to break this one.

Thank you all so much for the suggestions!

Peace, Love, Hugs.




Greg Norris
Software Developer & all around swell guy

http://www.GregNorris.net

__________________________________________________
Constructed from 100% recycled electrons.