litlitDM litlitDM - 6 months ago 13
SQL Question

mysql - Get distinct values from a Union of 2 sql queries

Good Morning,

I am trying to get all the distinct values from a union of two sql queries with this query based from their tag.

SELECT t.id, t.tag, bd.owner, bp.param_value as role
FROM baseData db
LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
WHERE bd.`status` = "active" AND bd.application = "template"
AND (((bp.param_value = "public" OR bp.param_value IS NULL)))
GROUP BY t.tag

UNION

SELECT t.id, t.tag, bd.owner, bp.param_value as role
FROM baseData bd
LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
INNER JOIN resTemplate cr ON cr.base_data_id = bd.id
WHERE bd.`status` = "active" AND bd.application = "template" AND cr.`status` = "active"
AND (((bp.param_value = "private" OR bp.param_value IS NULL)))
GROUP BY t.tag;


Both queries is from the same table, the only difference is their role which is "public" and "private". Private has a consideration from another table as well. This query returns this table

enter image description here

in this table, as you can see in red box, in the Tag column, Group is repeated. As well as some other Tag names.
How can i change my sql query to filter it so that Tag column is distinct?

Answer

Try this please;)

SELECT * FROM
    (SELECT t.id, t.tag, bd.owner, bp.param_value as role
    FROM baseData db
    LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
    RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
    WHERE bd.`status` = "active" AND bd.application = "template" 
    AND (((bp.param_value = "public" OR bp.param_value IS NULL)))

    UNION

    SELECT t.id, t.tag, bd.owner, bp.param_value as role
    FROM baseData bd
    LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
    RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
    INNER JOIN resTemplate cr ON cr.base_data_id = bd.id
    WHERE bd.`status` = "active" AND bd.application = "template" AND cr.`status` = "active"
    AND (((bp.param_value = "private" OR bp.param_value IS NULL)))) TMP
GROUP BY tag;
Comments