CODEWITHSUNDEEP
mysql
user2656943
user2656943

Reputation:

MYSQL how to select data from two lists where there is no matching data?

I have two tables that contain the following fields:

1) ID 2) Email

Table one and Table two have the same data however, Table two has less. I would like to create a new table where i select all the records from both tables where there is no matching email addresses. How would i do this?

Upvotes: 1

Views: 536

Answers (2)

Hardy
Hardy

Reputation: 1539

simply UNION two table, there will no 2 row with same data

for example if you have to table

[table X]
    ID  Email       
------  --------
     1  x       
     2  y       
     3  z       
     4  t   

[table Y]
    ID  Email       
------  --------
     1  x       
     6  g       
     2  v

query

SELECT ID,Email FROM X UNION SELECT ID,Email FROM Y

or

SELECT ID,Email FROM (SELECT ID,Email FROM X UNION SELECT ID,Email FROM Y) t

will give result

    ID  Email       
------  --------
     1  x       
     2  y       
     3  z       
     4  t       
     6  g       
     2  v

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Considering table1 is joined to table2 on column col1

Try this :::

Select 
col1, col2
from table1
left join table2 on (table1.col1 = table2.col1)
where table1.col2!=table2.col2 and table2.col1 is null

UNION 

Select 
col1, col2
from table2
left join table1 on (table1.col1 = table2.col1)
where table1.col2!=table2.col2 and table1.col1 is null

Upvotes: 1

Related Questions