SQL Help

Meeko · 2 Oct 2015 at 19:06

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

philldutton · 2 Oct 2015 at 19:57

A sample data set would be useful to be able to help.

dazzerd · 2 Oct 2015 at 20:39

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.

HungryHippos · 3 Oct 2015 at 10:53

Assuming there is some actual ID field in your table that means you can relate both tables.

SELECT * FROM Table A WHERE TableA.IDField NOT IN (SELECT IDField FROM Table B)

Trucamo · 5 Oct 2015 at 13:06

What you want to do is a FULL OUTER JOIN, like so:

Code:

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.Id = TableB.Id;

This is assuming Id models the relationship properly.

fretted · 5 Oct 2015 at 18:56

Trucamo said:
What you want to do is a FULL OUTER JOIN, like so:

Code:

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.Id = TableB.Id;

This is assuming Id models the relationship properly.

I think that he is not asking for an outer join here

planty · 5 Oct 2015 at 21:00

It's not clear what he's asking for until the OP provides the table structures!

over_gamed · 6 Oct 2015 at 09:20

Gives you duplicates? Try
"SELECT DISTINCT unique_column FROM table_A INNER JOIN table_B ON table_A.unique_column = table_B.unique_column"

Dolph · 6 Oct 2015 at 21:31

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.

over_gamed · 8 Oct 2015 at 21:37

is this solved?

Trucamo · 12 Oct 2015 at 00:30

fretted said:
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.