CODEWITHSUNDEEP
sqlms-access
Ajouve
Ajouve

Reputation: 10089

Select distinct on two tables

I'd like to select datas on tables without duplicates

For exemple

table1
data1; data2; data3; data4; data5

table2
data1; data2; data3

data1, data2, data3 are the same data type

table1

| data1    | data2    | data3    | data4    | data5    |
|----------|----------|----------|----------|----------|
| value1.1 | value2.1 | value3.1 | value4.1 | value5.1 |

table2

| data1    | data2    | data3    |
|----------|----------|----------|
| value1.1 | value2.1 | value3.1 |
| value1.2 | value2.2 | value3.2 |
| value1.3 | value2.3 | value3.3 |

I'd like to select data1, data2, data3 from my two tables with a distinct data1

| data1    | data2    | data3    |
|----------|----------|----------|
| value1.1 | value2.1 | value3.1 |
| value1.2 | value2.2 | value3.2 |
| value1.3 | value2.3 | value3.3 |

I'd like to have my result in only 3 columns for exemple value1.1 is from table1 and value1.2 is from table2

Upvotes: 0

Views: 129

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

In a nutshell, you want the following:

SELECT  data1, data2, data3
FROM    table1
UNION /* Union without optional ALL will eliminate duplicates accross the two tables */
SELECT  data1, data2, data3
FROM    table2

However, there is still something you need to clarify. You state:

I'd like to select data1, data2, data3 from my two tables with a distinct data1

The problem is that you don't say what should happen if you have a duplicate data1 accross the two tables.

Suppose table1 has a row with values (1, A, B) and table2 has a row with values (1, X, Y). Which row should be included in the final output?
The above query will include both rows because they differ in columns: data2 and data3.

EDIT for comment:

if duplicate I'd like to have the table1 result

You still need UNION, but you need to control which rows are to be included in the union. NOTE: Because your query will now ensure there are no duplicates, you can use UNION ALL so the DMBS doens't waste processing time trying to eliminate duplicates that don't exist.

SELECT  data1, data2, data3
FROM    table1
UNION ALL
SELECT  data1, data2, data3
FROM    table2
WHERE   NOT EXISTS (SELECT * FROM table1 WHERE table1.data1 = table2.data1)

Upvotes: 2

MinhD
MinhD

Reputation: 1810

You can try this. The UNION will get you DISTINCT result:

SELECT data1, data2, data3 FROM table1
UNION
SELECT data1, data2, data3 FROM table2;

Upvotes: 0

Related Questions