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
Nick--these 2 table need to have the same number of columns and datatypes
if you want to use union
--take the union out if they don't have the same number of columns and
datatypes
--you can also do this with 2 left or right joins or even a full outer
join
select *,'missing in Trade' as Missing
from Trade t
where not exists (select * from Cons c where c.Exc_Ref=t.Exc_Ref)
union all
select *,'missing in Cons' as Missing
from Cons c
where not exists (select * from Trade t where c.Exc_Ref=t.Exc_Ref)
--column mismatch
select * from Trade t
join Cons c on c.Exc_Ref=t.Exc_Ref
where c.Qty <> t.Qty
or c.Date <> t.Date
...
...
...
<rest of columns>
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx|||On Sep 6, 12:29 pm, 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
select * from Trade t
FOOL OUTER join Cons c on c.Exc_Ref=t.Exc_Ref
AND c.Qty = t.Qty
AND c.Date = t.Date|||"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1189110636.605076.236920@.r34g2000hsd.googlegroups.com...
>.
> select * from Trade t FOOL OUTER join Cons c ...
It is no easy task to personalize a query. Good job:)
www.beyondsql.blogspot.com