SQL Help

Associate
Joined
3 Apr 2003
Posts
442
Location
Dunfermline
Hi guys,

Wonder if anyone can help me out here with a bit of SQL.

I have two tables
Table A which contains partial records
Table B which contains complete records

I have performed a Union on the two tables to produce a master dataset but it this has highlighted a problem to me with duplicates...

Records may exist in Table A and NOT in Table B
Records may exist in Table A and Table B
Records may exist in Table B and NOT in Table A

I need to get all records from table B PLUS those that exist in Table A only.

Anyone able to help me out?

I can post a sample dataset and my current queries if it helps.


Cheers,
G
 
Associate
Joined
14 Mar 2007
Posts
1,659
Location
Winchester
Why you doing a union, from memory a union will only work if both sets have the same fields and the data types must at least be convertible.

Sounds to me you just need to do a Right Outer Join

Select leftTable.*, rightTable.Field1, rightTable.Field2
FROM leftTable
RIGHT JOIN rightTable
ON leftTable.KEY = rightTable.Key

If you don't have any related fields (or derivable related fields) then your screwed as your data isn't relational.
 
Man of Honour
Joined
17 Oct 2002
Posts
50,384
Location
Plymouth
Are joins really this complicated?

Select * from
Table a
Inner join
Table b
On a.Id = b.Id

Returns only records that appear in both tables.

Select * from
Table a
Left join
Table b
On a.id=b.id

Returns all records in table a and matching records in table b.

Select * from
Table a
Right join
Table b
On a.Id=b.id

Returns all records from table b and matching records in table a

Select * from
Table a
Full outer join
Table b
On a.Id=b.Id
Returns all records in table a and table b, linking the records where appropriate.

From the OP's description,it will be a left or right join required, depending on how the query is written.
 
Associate
Joined
7 Nov 2013
Posts
255
Location
Kent, England
I think that he is not asking for an outer join here

I had assumed from the following that this was what he was asking for.

Meeko said:
Records may exist in Table A and NOT in Table B
Records may exist in Table A and Table B
Records may exist in Table B and NOT in Table A

If not, my mistake.

As Dolph says, there's only a few combinations of joins, if your logic is more complex than a simple join, we'll need more information to be of help.
 
Back
Top Bottom