Mysql Union two tables with different values

Question

I need to return all the values from one table and then join this table to another query without duplicating values. This is my query so far.

    CREATE TABLE tmp_tbl (Id INT(11) PRIMARY KEY, Per VARCHAR(30), Des VARCHAR(50), Rol INT(11));
INSERT INTO tmp_tbl(Id, Per, Des, Rol)
SELECT DISTINCT
     p.p_id AS Id,
    p.p_title AS Per,
    p.p_description AS Des,
    '' AS Rol
    FROM perm p;

 SELECT  
    p.p_id AS Id,
    p.p_title AS Per,
    p.p_description AS Des,
    r.id AS Rol
    FROM perm p
    LEFT JOIN roles rp
        ON p.p_id = rp.p_id
    INNER JOIN rol r
        ON r.id = rp.r_id AND rp.r_id IN (101)
    UNION  ALL
SELECT * FROM
tmp_tbl;
 DROP TABLE tmp_tbl;

The statement returns all the rows from the first select but duplicates the rows on the union from the second select statement.

score 1 · Answer 1 · answered Jul 05 '12 at 07:28

1

try replacing the

UNION  ALL

with

UNION

union all will not remove duplicates.

answered Jul 05 '12 at 07:28

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Jul 05 '12 at 07:29

0

You should use UNION, not UNION ALL.
Infact UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

answered Jul 05 '12 at 07:29

Marco

56,740
14
129
152

score 0 · Answer 3 · answered Jul 05 '12 at 07:29

Use UNION DISTINCT to be sure :

(SELECT p.p_id AS Id, p.p_title AS Per, p.p_description AS Des, r.id AS Rol
FROM perm p
LEFT JOIN roles rp ON p.p_id = rp.p_id
INNER JOIN rol r ON r.id = rp.r_id AND rp.r_id IN (101))
UNION  DISTINCT
(SELECT * FROM tmp_tbl);

Mysql Union two tables with different values

3 Answers3