You had AND, you needed or. It was trying to find where CustomFieldID eq both 23 and 24 in same record.
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' ) OR ( B.CustomFieldID = 24 AND B.FieldValue = 'something else' ) ) At 11:18 AM 1/27/2003 -0700, you wrote: >Ok, next issue. :) > >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=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Get the mailserver that powers this list at http://www.coolfusion.com
