Professional Web Applications Themes

Need Help with Query of Queries - Coldfusion Database Access

I have the query "almost" there but I'm not sure exactly how to accomplish this. I need all recrods meeting the criteria from the AppliedLicense query. However not all records will have a match in the ApprovedLicense query. In the MergedData query I hope to get the Exp_Date column filled with the data from ApprovedLicense when there is a match but NULL if there is not a match. The code below, of course, only returns the records where there is a match in both data sources but I have no idea how to proceede. Thanks for any help. <CFQUERY NAME="AppliedLicense" ...

  1. #1

    Default Need Help with Query of Queries

    I have the query "almost" there but I'm not sure exactly how to accomplish
    this.

    I need all recrods meeting the criteria from the AppliedLicense query.

    However not all records will have a match in the ApprovedLicense query.

    In the MergedData query I hope to get the Exp_Date column filled with the data
    from ApprovedLicense when there is a match but NULL if there is not a match.

    The code below, of course, only returns the records where there is a match in
    both data sources but I have no idea how to proceede.

    Thanks for any help.



    <CFQUERY NAME="AppliedLicense" DATASOURCE="NEWtrackdata">
    Select
    Name,
    Type,
    City_State,
    Lic_Date,
    Comments,
    Track,
    Index,
    SSN,
    Pending
    From License
    Where Name Like '%#Trim(UCase(Session.NameString))#%'
    Order by Name
    </CFQUERY>

    <cfquery name="ApprovedLicense" datasource="#dns#">
    Select
    SSN,
    Exp_Date
    From L_Licens
    Where Exp_Date is not NULL
    and Rec_Date is not NULL
    and LName Like '%#Trim(UCase(Session.NameString))#%' or FName Like
    '%#Trim(UCase(Session.NameString))#%'
    </cfquery>

    <cfquery name="MergedData" dbtype="query">
    Select
    AppliedLicense.ssn as ssn,
    AppliedLicense.index as index,
    AppliedLicense.Pending as pending,
    AppliedLicense.TRACK as track,
    AppliedLicense.name as name,
    AppliedLicense.TYPE as type,
    AppliedLicense.City_State as City_State,
    AppliedLicense.Lic_Date as Lic_Date,
    AppliedLicense.Comments as Comments,
    MainLicense.Exp_Date as Exp_Date
    from rp, ApprovedLicense
    where AppliedLicense.ssn = ApprovedLicense.ssn
    </cfquery>

    LyndonPatton Guest

  2. #2

    Default Re: Need Help with Query of Queries

    I've never done a join on a query of queries but manybe this will work.

    SELECT
    AppliedLicense.ssn as ssn,
    AppliedLicense.index as index,
    AppliedLicense.Pending as pending,
    AppliedLicense.TRACK as track,
    AppliedLicense.name as name,
    AppliedLicense.TYPE as type,
    AppliedLicense.City_State as City_State,
    AppliedLicense.Lic_Date as Lic_Date,
    AppliedLicense.Comments as Comments,
    MainLicense.Exp_Date as Exp_Date
    FROM AppliedLicense
    LEFT JOIN ApprovedLicense
    ON AppliedLicense.ssn = ApprovedLicense.ssn
    </cfquery>

    Trevor
    [url]http://www.burnette.us[/url]

    TSB Guest

  3. #3

    Default Re: Need Help with Query of Queries

    Thanks Trevor,

    Since posting I've looked deeper into this and what I understand is that you
    can't use any kind of JOIN syntax in QofQ. (From the EasyCFM Fourm
    Administrator). It looks like my only hope is to simulate it somehow.

    I'm still hoping for any kind of idea.

    <cfquery name="MergedData" dbtype="query">
    Select
    AppliedLicense.ssn as ssn,
    AppliedLicense.index as index,
    AppliedLicense.Pending as pending,
    AppliedLicense.TRACK as track,
    AppliedLicense.name as name,
    AppliedLicense.TYPE as type,
    AppliedLicense.City_State as City_State,
    AppliedLicense.Lic_Date as Lic_Date,
    AppliedLicense.Comments as Comments,
    ApprovedLicense.Exp_Date as Exp_Date
    from AppliedLicense, ApprovedLicense
    where AppliedLicense.ssn = ApprovedLicense.ssn
    </cfquery>




    LyndonPatton Guest

  4. #4

    Default Re: Need Help with Query of Queries

    You could do it yourself with List functions if you hopefully don't have too
    many records. After running the second query:

    <cfset SSNList = ValueList(ApprovedLicense.SSN)>
    <cfset ExpList = ValueList(ApprovedLicense.ExpDate)> You now have 2
    coordinated lists

    Later on while processing the first query,
    <cfset idx = ListFind(SSNList,SSN)>
    <cfif idx GT 0>
    <cfset varExpDate = ListGetAt(ExpList,idx)>
    <cfelse>
    <cfset varExpDate = "none">
    </cfif>

    JMGibson3 Guest

  5. #5

    Default Re: Need Help with Query of Queries

    Since you can simulate many OUTER JOINS using a UNION, which is legal in
    Q-of-Q. Your first query in the Q-of-Q should SELECT your records where they
    exists in both tables, then UNION with a second SELECT where the records do NOT
    exits in the ApprovedLicense query, and replace any columns that would be from
    the ApprovedLicense query with a dummy constant and an alias matching the
    column name from the first SELECT.

    Phil

    paross1 Guest

  6. #6

    Default Re: Need Help with Query of Queries

    Thanks JM, it works great. I guess my typical query is small enought that the list size os ok.

    If I run into trouble later I'll try your UNION suggestion Phil.

    Thanks All.

    Lyndon
    LyndonPatton Guest

  7. #7

    Default Re: Need Help with Query of Queries

    Here is an example of the solution using UNION :

    <cfset SSNs = ValueList(ApprovedLicense.SSN)>
    <cfquery name="MergedData" dbtype="query">
    Select AppliedLicense.ssn
    , AppliedLicense.index
    , AppliedLicense.Pending
    , AppliedLicense.TRACK
    , AppliedLicense.name
    , AppliedLicense.TYPE
    , AppliedLicense.City_State
    , AppliedLicense.Lic_Date
    , AppliedLicense.Comments
    , ApprovedLicense.Exp_Date
    from AppliedLicense
    , ApprovedLicense
    where AppliedLicense.ssn
    = ApprovedLicense.ssn
    union all
    Select AppliedLicense.ssn
    , AppliedLicense.index
    , AppliedLicense.Pending
    , AppliedLicense.TRACK
    , AppliedLicense.name
    , AppliedLicense.TYPE
    , AppliedLicense.City_State
    , AppliedLicense.Lic_Date
    , AppliedLicense.Comments
    , NULL
    from AppliedLicense
    where AppliedLicense.ssn
    not in ( #ListQualify(SSNs)# )


    LyndonPatton Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. query of queries with avg()
    By gogl in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 6th, 09:50 PM
  3. 2 queries to 1 query
    By Samall in forum Macromedia ColdFusion
    Replies: 4
    Last Post: May 26th, 12:29 PM
  4. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  5. Query of Queries?
    By artists_envy in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 25th, 04:31 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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139