Relating queries

Posted on 2006-05-28
Last Modified: 2013-12-24
Hi- This should be simple for you experts..I hope!

I am pulling results from two different tables. I want the results to relate to each other:

<CFQUERY  NAME="get_products">
      SELECT      *
      FROM       table1
      WHERE active='1'
<CFQUERY  NAME="get_prices">
      SELECT      *
      FROM       table2


<cfoutput query="get_products" startrow=1 maxrows=1>#name#:</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput>
<cfoutput query="get_prices" startrow=1 maxrows=1>#price1#&nbsp; #price2#</cfoutput>

But I want prices in the "get_prices" query to relate to the name and description in the "get_product" query. I'm sure I need a where clause in my second query, that says something like WHERE id (From table1) equals product_id (from table2). But I'm not sure how to do that. Is that clear enough? Thanks!!
Question by:mel150
    LVL 18

    Accepted Solution


    You would normally do this in a join in a single query

    <CFQUERY  NAME="get_products">
        SELECT     *
        FROM      table1
        INNER JOIN table2 on table1.productCode = table2.productCode  
        WHERE active='1'


    Author Comment

    Thanks, Plucka- the easy and clearly expressed answer as usual!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now