SELECT table2.ChatID, table2.col2, table2.col3, table2.col4, table2.col5, MarkString = CASE when table1.ChatID IS NULL THEN 'redx.gif' ELSE 'checkmark.gif' END FROM table2 LEFT JOIN table1 ON table2.ChatID = table1.ChatID WHERE table1.user_id = #session.userid# [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => MikerRoo [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] => 7 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Extreme NOOB - Comparing Multiple queries - Coldfusion - Advanced Techniques

Extreme NOOB - Comparing Multiple queries - Coldfusion - Advanced Techniques

Hi! I need a hand with a little project I am working on. I am learning CF as I go, but this is stumping me. How do I compare the records of two different queries, find records that have data that matches in one particular column, and use this information to display an image in a table that shows the "main record set" with an extra column to hold the image that signifies that there was a match? Here is what I have tried so far: <CFQUERY Name="query1" Datasource="masterdb"> Select col1,col2,col3 from table1 Where user_id = #session.userid# </CFQUERY> <CFQUERY NAME="query2" ...

Sponsored Links
  1. #1

    Default Extreme NOOB - Comparing Multiple queries

    Hi!

    I need a hand with a little project I am working on. I am learning CF
    as I go, but this is stumping me.

    How do I compare the records of two different queries, find records
    that have data that matches in one particular column, and use this
    information to display an image in a table that shows the "main record set"
    with an extra column to hold the image that signifies that there was a
    match?

    Here is what I have tried so far:

    <CFQUERY Name="query1" Datasource="masterdb">
    Select col1,col2,col3 from table1
    Where user_id = #session.userid#
    </CFQUERY>

    <CFQUERY NAME="query2" DATASOURCE= "masterdb">
    SELECT col1,col2,col3,col4,col5 FROM table2
    </CFQUERY>

    <CFIF (#query1.col1#) EQ (#query2.col1#)>
    <img src="checkmark.gif">
    <cfelse>
    <img src="redx.gif">
    </CFIF>


    There "IS" data that matches in both col1's. I even output the
    resulting record sources on the page, and can see the matches, but the
    CFIF never puts the Checkmark image.

    Like I said, I am an exetreme Newbie (four days ago ColdFusion had
    something to do with Nuclear Physics) and just trying to hash this out.

    Thanks for your help!

    J

    Sponsored Links
    Jenny_in_MO Guest

  2. #2

    Default Re: Extreme NOOB - Comparing Multiple queries

    Hi Jenny

    If you use query1.col1 it means get the first entry from the query results. To
    get access to all the results from the query you need to loop over it or access
    each row individually.
    See attached code:

    I am not sure what you are trying to do with your queries. Perhaps if you give
    an example of the data.
    Does query2 only have one entry in it and your trying to check if the users
    entries match those from table2?

    Zoe

    LOOPING
    <CFLOOP QUERY="query1">
    #col1#<BR>
    </CFLOOP>
    OR
    <CFOUTPUT QUERY="query1">
    #col1#<BR>
    </CFOUTPUT>

    Access Each individual entry
    query1["col1"][1] -- get from row 1
    query1["col1"][2] -- get from row 2

    eg.
    <CFLOOP FROM="1" TO="#query1.recordcount#" INDEX="this">
    row #this# - #query1["col1"][this]#<BR>
    </CFLOOP>

    zoeski80 Guest

  3. #3

    Default Re: Extreme NOOB - Comparing Multiple queries

    You do not need to loop over the data.
    You can probably do this all in a query of queries or even in the original
    query itself.

    In order to help you we need to know what database you are using and more
    about the tables.

    Specifically, are the tables joined on some column (username for example)?

    It would help if you used meaningful column names here.

    Regards,
    -- MikeR


    MikerRoo Guest

  4. #4

    Default Re: Extreme NOOB - Comparing Multiple queries

    Following up on MikerRoo's thoughts, what about this?
    <CFQUERY Name="query1" Datasource="masterdb">
    Select col1,col2,col3, more_cols
    from table1, table2
    Where table1.col1 = table2.col1
    and user_id = #session.userid#
    </CFQUERY>

    Originally posted by: Jenny_in_MO
    Hi!

    I need a hand with a little project I am working on. I am learning CF
    as I go, but this is stumping me.

    How do I compare the records of two different queries, find records
    that have data that matches in one particular column, and use this
    information to display an image in a table that shows the "main record set"
    with an extra column to hold the image that signifies that there was a
    match?

    Here is what I have tried so far:

    <CFQUERY Name="query1" Datasource="masterdb">
    Select col1,col2,col3 from table1
    Where user_id = #session.userid#
    </CFQUERY>

    <CFQUERY NAME="query2" DATASOURCE= "masterdb">
    SELECT col1,col2,col3,col4,col5 FROM table2
    </CFQUERY>

    <CFIF (#query1.col1#) EQ (#query2.col1#)>
    <img src="checkmark.gif">
    <cfelse>
    <img src="redx.gif">
    </CFIF>


    There "IS" data that matches in both col1's. I even output the
    resulting record sources on the page, and can see the matches, but the
    CFIF never puts the Checkmark image.

    Like I said, I am an exetreme Newbie (four days ago ColdFusion had
    something to do with Nuclear Physics) and just trying to hash this out.

    Thanks for your help!

    J



    Dan Guest

  5. #5

    Default Re: Extreme NOOB - Comparing Multiple queries

    Dan,

    I had thought of that, but I need every record from table 2, and any record
    from table 1 that matches col 1 (in both tables) needs to trigger an image in
    the output, so my clients can see that as well.

    The image is just a checkmark, signifying that that record set matches a
    keyword that they specified.

    Jenny_in_MO Guest

  6. #6

    Default Re: Extreme NOOB - Comparing Multiple queries

    If you need every record from Table2 and matching records from Table1 you will
    need to do an outer join.

    Below is a modified version of Dan's example using a left outer join - this
    will give you ALL records from table 2 as well as values from table 1 if there
    is a match under the field alias "Table1Col1". You can then detect records
    which have a match by simply checking if Table1Col1 is empty or not.



    <cfquery name="query1" datasource="masterdb">
    SELECT Table2.col1, Table2.col2, Table2.col3,
    Table1.col1 AS Table1Col1
    FROM Table2 LEFT OUTER JOIN Table1 ON Table2.Col1 = Table1.Col1
    WHERE user_id = #session.userid#
    </cfquery>


    <cfloop query="query1">
    <cfif query1.Table1Col1 neq "">
    <img src="checkmark.gif">
    <cfelse>
    <img src="redx.gif">
    </cfif>
    </cfloop>

    efecto747 Guest

  7. #7

    Default Re: Extreme NOOB - Comparing Multiple queries

    Okay, if we assume...
    1) The first table relates clients to chat topics they are interested in.
    2) The second table lists all "chats".
    3) The mark indicates interest in that chat.
    4) I will call "col1" "ChatID".

    Then, you can do the following:
    (see attached -- no looping required!)


    <CFQUERY Name="query1" Datasource="masterdb">
    SELECT
    ChatID,col2,col3
    FROM
    table1
    WHERE
    user_id = #session.userid#
    </CFQUERY>
    <!--- (No change) --->

    <CFQUERY NAME="query2" DATASOURCE= "masterdb">
    SELECT
    table2.ChatID,
    table2.col2,
    table2.col3,
    table2.col4,
    table2.col5,
    MarkString = CASE when table1.ChatID IS NULL THEN 'redx.gif' ELSE
    'checkmark.gif' END
    FROM
    table2
    LEFT JOIN
    table1 ON table2.ChatID = table1.ChatID
    WHERE
    table1.user_id = #session.userid#
    </CFQUERY>

    MikerRoo Guest

  8. #8

    Default Re: Extreme NOOB - Comparing Multiple queries

    What is the MARKSTRING? is it a variable that will store the image name, that I need to include in my <CFOUTPUT>?
    Jenny_in_MO Guest

  9. #9

    Default Re: Extreme NOOB - Comparing Multiple queries

    Yes.
    MikerRoo Guest

  10. #10

    Default Re: Extreme NOOB - Comparing Multiple queries

    Thanks Mikeroo ~ with a little bit of tweaking (and a suggestion to check char types) it is doing what I needed!

    Thanks again!

    Jen


    Jenny_in_MO Guest

Similar Threads

  1. Replies: 7
    Last Post: April 10th, 06:05 AM
  2. Multiple Queries
    By score1 in forum Coldfusion Database Access
    Replies: 13
    Last Post: September 12th, 12:35 PM
  3. Multiple DSN queries
    By loucas in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 18th, 08:45 PM
  4. CF Multiple Queries, Multiple Records
    By OnDemand Junkie in forum Macromedia ColdFusion
    Replies: 2
    Last Post: July 4th, 07:44 AM
  5. comparing queries to flag conflicts
    By Alpay Eno in forum ASP Database
    Replies: 1
    Last Post: July 26th, 08:55 PM

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
  •