All I can quickly come up with would be to use self-joins for every attribute/value pair.
select A.DocId from attrib_xref A inner join attrib_xref B on A.DocID = B.DocID where A.attribId = 12 and B.attribId = 24 and A.attribValue = 'some text' and B.attribValue = 'some other text' ... You'll have to dynamically write out the sql, adding another self join for every pair you want to search for. It should work, but it's gonna be dog slow I would think. Hopefully someone else can find a better solution for you... </rob> -----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 ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
