CODEWITHSUNDEEP
sqlpostgresql
Chris
Chris

Reputation: 527

join two different tables and remove duplicated entries

I'm complete novice in sql queries. I have two tables:

table1:

id_s  name   post_code     city     subject
------------------------------------------
1     name1  postal1    city1    subject1
2     name2  postal2    city2    subject2
3     name3  postal3    city3    subject3
4     name4  postal4    city4    subject4
...
~350

table2:

id_p  name   post_code     city     subject
------------------------------------------
1     name1  postal1    city1    subject1
2     name2  postal2    city2    subject2
3     name3  postal3    city3    subject3
4     name4  postal4    city4    subject4 
...
~1200

I want to join both tables, and remove entries with same name and postal code. I found some answers on how to do it but they were too complicated.

Upvotes: 4

Views: 46155

Answers (3)

bhupesh
bhupesh

Reputation: 1

Try this simple one:

(select * from table1 MINUS select * from table2)
UNION
(select * from table2 MINUS select * from table1)

Upvotes: 0

Saju
Saju

Reputation: 3257

You can give a SELECT INTO command like this

SELECT * INTO newtable FROM table1
UNION
SELECT * FROM table2;

This will create a newtable from both table1 and table2 without any duplicates

Upvotes: 1

rs.
rs.

Reputation: 27427

You can use UNION clause, UNION will check for duplicates and only distinct rows will be returned

SELECT * FROM table1
UNION
SELECT * FROM Table2

Edit: To store data from both table without duplicates, do this

INSERT INTO TABLE1
SELECT * FROM TABLE2 A
WHERE NOT EXISTS (SELECT 1 FROM TABLE1 X 
                  WHERE A.NAME = X.NAME AND 
                  A.post_code = x.post_code)

This will insert rows from table2 that do not match name, postal code from table1

Alternative is that You can also create new table and not touch table1 and table2

CREATE TABLE TABLENAME AS
SELECT * FROM table1
UNION
SELECT * FROM Table2

Upvotes: 8

Related Questions