1) There IS a matching-ID record in the second table, and the field value
of this record is not a match for my checking criteria
just do a where clause with the reverse criteria
2) There is NO matching-ID record in the second table at all, hence no
value to check against (which means this record would qualify)
just do a check for null values. if you using a left join then the other
table will have a null value.
Take the below example:
Table name: Table1
Data elements:
ID
SomeData
Table name: Table2
Data elements:
ID
SimilarData
Original select statement
select table1.ID, table2.Similardata
from table1.ID left join table2.ID on table1.ID = table2.ID
where table1.ID = 'generic'
Your new select statement
select table1.ID, table2.Similardata
from table1.ID left join table2.ID on table1.ID = table2.ID
where table1.ID <> 'generic' or table2.id is null
Hope that helps.
Casey Cook
Steven Monaghan
<MonaghaS To: SQL <[EMAIL PROTECTED]>
@mscdirect.com> cc:
Subject: RE: How to...
02/06/03 12:18
PM
Please respond
to sql
Look into the MINUS set operator (the opposite of UNION).
That may steer you in the right direction...
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Scott Weikert [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 06, 2003 1:20 PM
To: SQL
Subject: How to...
(this is SQL7 btw)
Got an odd issue and I'm not 100% sure how to tackle it.
I have two tables, linked by a pair of values. Querying to get all the
records out of the first table where I find a record in the second table,
with certain IDs and a certain value in a field, no sweat. Doing that with
a right outer join. Doing multiple second-table checks like this is also no
problem.
Now I'm trying to figure out something of the opposite - where a value in
the second table is NOT what I'm checking on. My issue is, I don't have a
record in the second table with the ID criteria from the first table, for
every record in the first table. So what I need to do in these cases is
check for either:
1) There IS a matching-ID record in the second table, and the field value
of this record is not a match for my checking criteria;
2) There is NO matching-ID record in the second table at all, hence no
value to check against (which means this record would qualify)
Any nudges in the right direction would be most appreciated.
Thanks --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
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
