CODEWITHSUNDEEP
sqlcoldfusion
Betty Mock
Betty Mock

Reputation: 1393

Can SQL queries be combined?

I have to go into a table to retrieve a parameter, then go back into the same table to retrieve data based on the parameter.

<cfquery name = "selnm" datasource = "Moxart">
 select SelName from AuxXref 
 where Fieldname = <cfqueryparam value = "#orig#">
</cfquery>

<cfset selname = selnm.SelName>

<cfquery name = "fld" datasource = "Moxart">
select Fieldname, DBname from AuxXref 
where SelName = <cfqueryparam value = "#selname#">
</cfquery>

Can this be done in a single query?

Upvotes: 1

Views: 139

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this might satisfy your requirements.

select fieldname, DBname
from AuxXref
where selname in 
(select distinct selname
 from auxXref
 where fieldname = <cfqueryparam value = "#orig#">
)
and fieldname <> <cfqueryparam value = "#orig#">

If the subquery returns more than one row, and you only want one, then you'll have to specify which one you want.

Upvotes: 4

Scott Stroz
Scott Stroz

Reputation: 7519

You can do this in one query like so:

<cfquery name = "fld" datasource = "Moxart">
   select Fieldname, DBname, SelName from AuxXref 
   where SelName = <cfqueryparam value = "#orig#">
   AND FieldName = <cfqueryparam value = "#orig#"> 
</cfquery>

Upvotes: 2

Related Questions