|
|
Posted by Nick on 09/07/07 10:58
On 6 Sep, 20:06, Roy Harvey <roy_har...@snet.net> wrote:
> 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.shirwal...@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- Hide quoted text -
>
> - Show quoted text -
Thanks Roy ! Your solution is too perfect.
Thanks
Navigation:
[Reply to this message]
|