(I posted this to the SQL list, but since it gets about as much traffic as... well, very little traffic... I'm posting here as well.)
I have two tables that I'm trying to do a combined select on, based on criteria drawn from both tables. Second table has zero-to-X records for each record out of the first table, and I'm doing a left outer join on'em - that works fine. If I look for the presence of one bit of data in the second table, it works great. If I look for the presence of TWO bits of data (i.e. two matching records) out of the second table, it doesn't - it appears that the two records 'cancel each other out', as I'm looking for an ID and a value in a text field - i.e. the first record's ID doesn't match the second ID I'm looking for, and the second record's ID doesn't match the first ID I'm looking for, so even though the ID/values match, they both get excluded. Here's a trimmed down query: SELECT DISTINCT A.*, B.FieldID, B.FieldValue FROM TableA A LEFT OUTER JOIN TableB B ON A.KeyedField = B.KeyedField WHERE 1 = 1 AND ( ( B.CustomFieldID = 23 AND B.FieldValue = 'something' ) And ( B.CustomFieldID = 24 AND B.FieldValue = 'something else' ) ) Thanks in advance, --Scott ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
