Complicated query

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
NickSomething 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.comwrote:

Quote:

Originally Posted by

>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

|||On 6 Sep, 20:06, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

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.comwrote:

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

I need to write a query which should give me records :

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Please help me to resolve this complicated query.

>

Quote:

Originally Posted by

Thanks
Nick- Hide quoted text -

>
- Show quoted text -

Thanks Roy ! Your solution is too perfect.

Thanks