perhaps something like this
I'm sure there is a better way but this may be a start.
HTH
Select AttribValue
>From MyTable
Where 0 = 0
<cfif isdefined('attributes.AttribID') and isdefined('attributes.DocID') >
And (
<cfset lc = 0>
<cfloop list="#attributes.AttribID#" index='x'>
<cfloop list=#attributes.DocID# index='y'>
<cfset lc = #lc#+1>
<cfif lc eq 1>
(MyTable.AttribID = #x# and MyTable.DocID = #y#)
<cfelse>
OR (dbo.personnel_appointment.department_id = #x#)
</cfif>
</cfloop>
</cfloop>)
</cfif>
David DiPietro
Systems Developer / Engineer
OSU College of Medicine & Public Health
Voice (614) 292-5960
Fax (614) 292-0745
-----Original Message-----
From: Dirk Sieber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.
I've got a collection of tables, among which are document, and attrib_xref.
In the attrib_xref table, there's (among others) the following columns:
DocID
AttribID
AttribValue
Each document can have multiple attributes, so there may be many lines in
this table, with the same DocID, but different AttribID/AttribValue pairs.
What I'd like to be able to do is an "and" search for multiple attributes,
so I'd like to be able to say that I'm looking for the document with
DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND
where there's also a second Attrib_XRef record with DocID=x, where
AttribID=24 and it's corresponding value is 'some other text'
I also need this to be extensible - ie, a person may specify one attribute
pair, or 2, or 3, or... etc.
Any suggestions (short of re-designing the DB - that's out of my control,
unfortunately).
If someone can point me in the right direction, I'd really appreciate it...
Thanks,
Dirk
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
