Need Help with Query of Queries

[LyndonPatton]

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>

[TSB]

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]

[LyndonPatton]

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>

[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>

[paross1]

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

[LyndonPatton]

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]

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)# )