• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

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!
0
jollymon6672
Asked:
jollymon6672
1 Solution
 
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
 
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now