Complicated query

[Nick]

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

[Roy Harvey]

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
<na*****************@gmail.comwrote:

>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

[Nick]

On 6 Sep, 20:06, Roy Harvey <[e-mail]:

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

<[e-mail]:

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