B
B
Binarix2015-10-05 08:37:30
MySQL
Binarix, 2015-10-05 08:37:30

How to select data from two table without repeats?

Hello. There are 2 mysql tables - tb1 and tb2, they contain the full name of people and their email. You need to make a selection without duplicate email.
tb1
name email
Ivanov [email protected]
Petrov [email protected]
Sidorov [email protected]
tb2
name email
Smirnov [email protected]
Kuznetsov [email protected]
Petrov [email protected]
The result should be
name email
Ivanov [email protected] mail.ru
Petrov [email protected]
Sidorov [email protected]
Smirnov [email protected]
Kuznetsov [email protected]
The duplicates themselves came out like this

SELECT * FROM `tb1`
JOIN `tb2`
ON tb1.email = tb2.email

Answer the question

In order to leave comments, you need to log in

3 answer(s)
E
Evgeniy Zavyalov, 2015-10-05
@profit

within the example

select * from tbl1
union
select * from tbl2

R
Rsa97, 2015-10-05
@Rsa97

SELECT `e`.`email`, IFNULL(`t1`.`name`, `t2`.`name`) AS `name`
    FROM (
        SELECT DISTINCT `email` FROM `table1`
            UNION DISTINCT SELECT `email` FROM `table2`
    ) AS `e`
    LEFT JOIN `table1` AS `t1` ON `t1`.`email` = `e`.`email`
    LEFT JOIN `table2` AS `t2` ON `t2`.`email` = `e`.`email`

A
Alex Safonov, 2015-10-05
@elevenelven

SELECT
    name, email
FROM
    table_1
WHERE
    email NOT IN (
        SELECT DISTINCT email FROM table_2
    );
UNION
SELECT
    name, email
FROM
    table_2
WHERE
    email NOT IN (
        SELECT DISTINCT email FROM table_1
    );

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question