Went round and round with my co-worker on this. I can't simply use "OR". I need to check that BOTH are true. That the value tied to ID #23 is what is specified, AND that the value tied to ID #24 is what is specified. In my testing environment, for the record I'm trying to get out of TableA, I've got *five* records in TableB - #23 and #24 are just the two I'm checking against in this case.
I think it's a matter of restructuring the query in some fashion, but I haven't hit on the solution yet. At 01:30 PM 1/27/2003 -0600, you wrote: >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
