Re: Full outer join and Union again !!

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 05/19/04 

Date: Wed, 19 May 2004 17:36:57 +1000

You want all the rows from both tables? Matched where possible?

Create two queries: A left join B, B left join A
Choose the columns in the two queries so that the column order is the same.

Union the two queries:

select * from c
union
select * from d

'Union' rejects all duplicate records, so your result will be
A without B
B without A
A with B

Is this what you want?

(david)

"Adel" <annw7@hotmail.com> wrote in message
news:31DC1F8A-B610-40CB-BA3D-2F8C7F8FCC5C@microsoft.com...
> Hello
> I'm trying something here whotch might be simple for you but it just
didn't work for me and I need it to be done badlly
> I have 2 tables that are Transaction table which has about 40 col and
about 70000 rows this table has many primary keys but what I care about here
are project_id and transaction_id and I have anoher table called budget
witch has anther 40 col and about 3000 rows but there is primary key created
for this table it's an excel *** and I export it to a database table using
SQL server
> so what I'm trying to do here is to select all the rows from both table to
create a crystal report
> I have tried the left outer join assuming that my transaction table is the
left one so that displays all the records from the tranaction table and only
the matched records from the budget table where the project_id and the
transaction_id are matches on both table but it ignores the records not
matching in the budget table.
> When I tried the right outer join you know it works vice versa so I still
can't get all the records from both table
> So here I tried to use the Full outer join but unfortunatly it's not
giving me the right result and it still ignores some rows from both tables,
as I'm not so experinced guy, I tried to understand some of your threds
taking about duplicating some row and yes I remember when I was trying to
use the union all I got some dupliactes , but I still can't get the right
combination of using the full outer join with the union in my select
statment
> It would be very appreciated if you can help me, since the result I'm
expecting it supose to be the total records of both tables (about 73000) but
what I'm getting now is about 71000 records and I just can't know which
records were dropped and from witch table and what is the reason !!
> Thanks in advance