SQL Woes

[MorganGreylock]

Heres my SQL:

select * from tblStudentRequests
where GottaHave = '#search_text#'
or TwoMoInst1 = '#search_text#'
or TwoMoComm1 = '#search_text#'
or OneMoPat1 = '#search_text#'
or Longin = '#search_text#'


What I need to do now is figure out WHICH of the following fields (TwoMoInst1, TwoMoComm1, etc) contained the search_text value. Short of doing a huge CFIF section, I'm not sure if it can be done in the first place... any suggestions?

TIA

MG

 

[MarcDePoe]

Is this needed for displaying search results to the user?

If you let me know exactly why you need to know which field(s) matched, perhaps I can help you better ...

Marc

 

[crafter]

Given the fact that you're doing what you're doing the way you're doing it, your database design may be hindering your progress. A well-designed database avoids redundant data. I agree with Marc. More information is needed to help you out.

 

[CFDude]

I agree with crafter that you would probably benefit from a better db design, but if you are like me, you may be stuck with it

At any rate, I had to do something like this a while ago, and while it is not elegant by any stretch, you could create a query with multiple unions and pass a static text of the colum name -- e.g
<cfquery name=&quot;myQry&quot; datasource=&quot;foo&quot;>
Select field1,field2, 'TwoMoInst1' AS SQLSource
From MyTable
Where TwoMoInst1 = '#search_text#'
UNION
Select field1,field2, 'TwoMoComm1' AS SQLSource
From MyTable
Where TwoMoComm1 = '#search_text#'

UNION
Select field1,field2, 'OneMoPat1' AS SQLSource
From MyTable
Where OneMoPat1 = '#search_text#'

UNION
Select field1,field2, 'Longin' AS SQLSource
From MyTable
Where Longin = '#search_text#'

</cfquery>
Search Results

<cfoutput query=&quot;Qry1&quot;>
#field1# #field2# -- This came from column #SQLSource#
</cfoutput>