Skip to main content
LyndonPatton
Inspiring
April 18, 2006
Answered

Need Help with Query of Queries

  • April 18, 2006
  • 6 replies
  • 898 views
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.

This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer JMGibson3
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>

6 replies

LyndonPattonAuthor
Inspiring
April 18, 2006
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>


JMGibson3Correct answer
Participating Frequently
April 18, 2006
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>
paross1
Participating Frequently
April 18, 2006
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
TSB
Inspiring
April 18, 2006
I've never done a join on a query of queries but maybe this will work.

<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 AppliedLicense
LEFT JOIN ApprovedLicense
ON AppliedLicense.ssn = ApprovedLicense.ssn
</cfquery>

Trevor
Hello
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices