Query within a Query

[wilhelm]

:confused;
Is it possible to do a query within a query to where the data wanted is in 2
seperate Datasources? The reason is that we are being asked to combine info
from 2 seperate databases and would not like to use looping if possible.
code example
<cfquery name="quepeople" datasource="database1">
Select name, address, shift, supervisor, idnumber
From employeeinfo
where
<cfquery name="queawardrecipeant" datasource="database2">
Select idnumber
from Awardtable
where presented <> 0
<cfquery>
<cfquery>

[jdeline]

Sorry, no can do.

[mxstu]

Depending on what type of databases you're talking about, it may be possible to
run a query on tables that reside in two different databases. In SQL Server,
you can query tables in two different databases or on two different linked
servers, just as if they were objects in the same database. You just need to
qualify the object names with the server and database names of each objects:

--- psuedo code
SELECT db1.ColumnID AS LocalColumn, db2.ColumnID AS RemoteColumn
FROM localServerName.databaseName.owner.tableName db1,
remoteServerName.databaseName.owner.talbeName db2
WHERE (some conditions....)

[MikerRoo]

You can easily accomplish something similar using query of queries -- although
this may not be as efficient as querying two DB's simultaneously.

See the code below:

Regards,
-- MikeR


<cfquery name="quepeople" datasource="database1">
SELECT name, address, shift, supervisor, idnumber
FROM employeeinfo
</cfquery>


<cfquery name="queawardrecipeant" datasource="database2">
SELECT idnumber
FROM Awardtable
WHERE presented <> 0
</cfquery>


<cfquery name="qGetAwardVictims" dbtype="Query">
SELECT DISTINCT
quepeople.*
FROM
quepeople,
queawardrecipeant
WHERE
quepeople.idnumber IN (queawardrecipeant.idnumber)
</cfquery>

[OldCFer]

Another way would be to create a view in your DB linking the two tables.

[Alen]

you may be able to use ValueList(query.column) to make a list and then use that list like this:

WHERE idnumber IN ( #ValueList(query.column)# )

[wilhelm]

How would you go about doing this. Would you create a list (or array) for the first then use the list as part of the wuery for the 2nd one or how.

[mxstu]

I believe allan was suggesting that you could run the first query, and then use
the ValueList() function to create a list of ID's that you could then use in
your second query.





<!--- note - this does not handle an empty list --->
<cfquery name="getFirstTableData" datasource="yourFirstDSN">
SELECT ID, SomeOtherColumns
FROM FirstDataSourceTable
</cfquery>

<cfset IDList = ValueList(getFirstTableData.ID)>

<cfquery name="getSecondTableData" datasource="yourSecondDSN">
SELECT ID, SomeOtherColumns
FROM SecondDataSourceTable
WHERE ID IN (#IDList#)
</cfquery>

[TA-Selene]

Depending on the number of values that could returned, using a query of a query
as MikerRoo suggeted may be your best bet. There is a limit on the lenght of a
list you can pass into a query and speed can be a major factor. I once had a
developer pass in a list like this and it had thousands of values. Needless to
say I rewrote the query to be more efficient.