Posted by Roy Harvey on 09/06/07 19:06

Something along these lines should do it.

SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <> '
End as Compare,
A.OtherCol1, B.OtherCol1,
....
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <> B.OtherCol1
OR ...
OR A.OtherCol9 <> B.OtherCo9

This assumes that Exc_Ref is the unique key to both tables.

Roy Harvey
Beacon Falls, CT

On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<nachiket.shirwalkar@gmail.com> wrote:

>Hi,
>
>I have two tables Trade table and Cons table. Records are inserted in
>both the tables independent of each other. There are fields like
>Exc_Ref, Qty, Date in both the tables.
>
>I need to write a query which should give me records :
>
>1. Where there is missing Exc_Ref value in either of the table. i.e.
>If Trade table has a Exc_Ref value but missing in Cons table then that
>record should be displayed. Similarly if Cons has a Exc_Ref value
>which is not found in Trade table then that too should be displayed.
>
>2. In case where both the tables have matching Exc_Ref data then it
>should display the record only when the remaining column does not
>match like Qty or Date.
>
>Please help me to resolve this complicated query.
>
>Thanks
>Nick

[Back to original message]