Proper way to Join tables in CFQuery

I am trying to join tables in a CFQUERY and it is failing
SELECT *
FROM Products LEFT OUTER JOIN (Colors ON Products.ProductID = Colors.ProductID)
(LEFT OUTER JOIN Sizes ON Products.ProductID = Sizes.ProductID)
WHERE CategoryID = #URL.CategoryID#
ORDER BY CategoryID ASC

Help!
jollymon6672Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wytcomCommented:
You may have a problem with select * here since the joined tables have the same field names.  Try explicitly naming the fields to select.  But for any field that appears in more than one table you have to specify which table to use:

SELECT Products.ProductID, thisField, thatField, Colors.CommonField
FROM Products LEFT OUTER JOIN (Colors ON Products.ProductID = Colors.ProductID)
(LEFT OUTER JOIN Sizes ON Products.ProductID = Sizes.ProductID)
WHERE CategoryID = #URL.CategoryID#
ORDER BY CategoryID ASC
0
danrosenthalCommented:
Try this...

SELECT p.fieldname, c.fieldname, s.fieldname
FROM (Products p
LEFT OUTER JOIN Colors c ON p.ProductID = c.ProductID)
LEFT OUTER JOIN Sizes s ON p.ProductID = s.ProductID
WHERE p.CategoryID = #URL.CategoryID#
ORDER BY p.CategoryID ASC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCorfmanCommented:
I agree with danrosenthal, the problem is the way the () are in your original query... his example should work.

You dont 'HAVE' to explicitely call out the columns that you want, but you should.

I would also note that using the
= #URL.CategoryID# is asking for problems... what if the user puts in, on the URL line for your pages...
?CategoryID=10%20or%201=1
This will actually retrieve everything in the database... it is called sql injection, and is one of my beefs...

You should never directly use URL for FORM parameters in the query with just #var#....

It is very easy to utilize the <cfqueryparam ....> to avoid this type of problem...
WHERE p.CategoryID = <cfqueryparam value="#URL.CategoryID#" cfsqltype="CF_SQL_NUMERIC">

With strings, it also takes care of all the single/double quote problems that can be a real hastle...
Lastly, if you are using a database like Oracle, or one of the other heavy duty databases, this will allow cold fusion to pass in bind variables instead of literals... this will give a significant performance boost.

I know this isn't directly related to your question, but I strongly encourage you, or anyone else reading this, to use <cfqueryparam> always, instead of form or url parameters directly embedded in the sql.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.