>>>Is there a SQL way of asking if a record in table 1 does not have a match in table 2?
>>>
>>>
>>>TIA
>>>
>>>Chris
>>
>>select a.key, b.key, ;
>>isnull(a.key) as 'NotInOld', ;
>>isnull(b.key) as 'NotInNew' ;
>> from old a full join new b on a.key=b.key
>>
>>Cetin
>
>
>
>
>
>select a.grade,a.titlecode,a.cocode,b.grade,b.titlecode, ;
>isnull(a.titlecode) as 'notinold' ;
>from c:\sys2data\tlkpttlgrade a full join c:\sys2data\tlkpttlgrade.old b ;
>on a.titlecode = b.titlecode and a.cocode = b.cocode ;
>where (a.titlecode = b.titlecode) and (a.cocode = b.cocode) and (a.grade <> b.grade) ;
>into table c:\sys2data\tblttlgradechanges4
>
>
>there is a record in b that has a cocode that exists in a but a titlecode.
>It does not get selected by the logic?
>
>
>
>Chris
Chris,
Repeating (a.titlecode = b.titlecode) and (a.cocode = b.cocode) in where forces 'full join' back to 'inner join'. I'm not clear about what you want to select but try this. This would select all Grade Changes regardless titlecode+cocode exists in other :
select ;
a.grade as GradeA, a.titlecode as TitleA,a.cocode as CodeA,;
b.grade as GradeB, b.titlecode as TitleB,b.cocode as CodeB,;
isnull(a.titlecode) as 'notinA',;
isnull(b.titlecode) as 'notinB' ;
from tlkpttlgrade a ;
full join tlkpttlgrade.old b ;
on a.titlecode = b.titlecode and a.cocode = b.cocode ;
where a.Grade # b.Grade
Cetin