SELECT Categories.Name, Categories.Category_ID FROM Categories SELECT ID, Product_ID, Category_ID FROM Product_Category WHERE Category_ID = #CATS.Category_ID# SELECT NAME FROM Products WHERE Product_ID = #PRODCAT.Product_ID# Thanks D [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => OnDemand Junkie [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> SELECT Name FROM Products WHERE Product_ID IN (#ValueList(PRODCAT.Product_ID)#) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> CF Multiple Queries, Multiple Records - Macromedia ColdFusion

CF Multiple Queries, Multiple Records - Macromedia ColdFusion

All, I've got three queries all passing variables from previous queries. The issues? The second query returns 4 records. I want to use IDs for these records in query #3. However what I have only returns only 1 record. I want all 4 ID values in query #3 <!-- GET EVERYTHING FROM CATEGORIES --> <!-- QUERY ONE --> <cfquery name="cats" datasource="cfweb" maxrows="5"> SELECT Categories.Name, Categories.Category_ID FROM Categories </cfquery> <!-- GET INFO FROM PRODCAT WHERE CATID = CATS.CATID THIS SHOULD RETURN A LIST OF PROD IDS --> <!-- QUERY TWO --> <cfquery name="PRODCAT" datasource="cfweb"> SELECT ID, Product_ID, Category_ID FROM Product_Category WHERE ...

  1. #1

    Default CF Multiple Queries, Multiple Records

    All,

    I've got three queries all passing variables from previous queries. The
    issues?
    The second query returns 4 records. I want to use IDs for these records in
    query #3.
    However what I have only returns only 1 record. I want all 4 ID values in
    query #3

    <!-- GET EVERYTHING FROM CATEGORIES -->
    <!-- QUERY ONE -->
    <cfquery name="cats" datasource="cfweb" maxrows="5">
    SELECT Categories.Name, Categories.Category_ID
    FROM Categories
    </cfquery>
    <!-- GET INFO FROM PRODCAT WHERE CATID = CATS.CATID THIS SHOULD RETURN A LIST
    OF PROD IDS -->
    <!-- QUERY TWO -->
    <cfquery name="PRODCAT" datasource="cfweb">
    SELECT ID, Product_ID, Category_ID
    FROM Product_Category
    WHERE Category_ID = #CATS.Category_ID#
    </cfquery>
    <cfquery name="PROD" datasource="cfweb">
    SELECT NAME
    FROM Products
    WHERE Product_ID = #PRODCAT.Product_ID#

    Thanks

    D

    OnDemand Junkie Guest

  2. #2

    Default Re: CF Multiple Queries, Multiple Records

    You can use the CF ValueList() function to create a comma delimited list of all
    IDs returned by the previous query. Then use the SQL IN() function to return
    all records where the column value equals any one of the IDs in the returned
    list.

    Do you need to keep the data in separate queries? You could probalby combine
    your 3 query statements into 1.

    <!--- needs error handling - ex. if "PRODCAT" query returns 0 records --->
    SELECT Name
    FROM Products
    WHERE Product_ID IN (#ValueList(PRODCAT.Product_ID)#)

    mxstu Guest

  3. #3

    Default Re: CF Multiple Queries, Multiple Records

    Hi

    Assuming you don't need all three queries, you could do something like this

    SELECT
    Cat.Name, Cat.Category_ID ,
    prodcat.ID, prodcat.Product_ID, prodcat.Category_ID,
    prod.NAME
    FROM
    Categories cat,
    Product_Category prodcat,
    Products prod
    Where
    prodcat.Category_ID = cat.Category_ID and
    prod.Product_ID = prodcat.Product_ID

    stephend Guest

Similar Threads

  1. multiple queries and cfgrid
    By rmorgan in forum Coldfusion Database Access
    Replies: 1
    Last Post: October 11th, 04:13 AM
  2. multiple queries in a cfmail tag ?
    By mo280379 in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: August 8th, 04:09 AM
  3. Multiple DSN queries
    By loucas in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 18th, 08:45 PM
  4. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  5. multiple records per row
    By DotKom in forum PHP Development
    Replies: 1
    Last Post: May 11th, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •