Proper way to Join tables in CFQuery

Posted on 2006-04-14
Last Modified: 2013-12-24
I am trying to join tables in a CFQUERY and it is failing
FROM Products LEFT OUTER JOIN (Colors ON Products.ProductID = Colors.ProductID)
(LEFT OUTER JOIN Sizes ON Products.ProductID = Sizes.ProductID)
WHERE CategoryID = #URL.CategoryID#

Question by:jollymon6672
    LVL 7

    Expert Comment

    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
    LVL 15

    Accepted Solution

    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
    LVL 16

    Expert Comment

    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...
    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.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now