mysql DISTINCT关键字不适用于多列连接表

Question

I have used mysql DISTNCT keyword for one of my mySQL query for avoid duplication of data displaying on search box.

But when I add multiple column names on SELECT it doesn't work.

Please advice how to proceed.

$query = "

    SELECT * FROM (
        SELECT DISTINCT b.title,b.id, b.metakey
            FROM categories b
            WHERE  b.title like '%".$searchc."%' AND b.parent_id BETWEEN 84 AND 107 AND  b.level=3 ORDER BY LOCATE('".$searchc."', REPLACE(b.title, ' ', '')), b.title
    ) CLASS_CAT
        UNION ALL
            SELECT * FROM (
                SELECT DISTINCT a.title,  a.id, a.title as metakey
                    FROM content a join
                      categories b
                      on a.categories_id = b.id
                WHERE REPLACE(a.title, ' ', '') like '%".$searchc."%' 
                AND b.parent_id BETWEEN 84 AND 107 AND b.level=3 
             ) CLASS_ITEM
";

Answer 1

SELECT DISTINCT will remove duplicates from the one SELECT statement. UNION ALL directs the system to not look for duplicates between the two sets you are combining (each SELECT).

Use UNION (without ALL) instead. Note that removing the check for duplicates is faster, so if you know a set you're querying is unique, skip the dup check.

Also note that by row duplicates I'm referring to every column in every row. If any column makes a row unique it will appear in the result set. If you only want some columns to be unique you'll need to GROUP BY and aggregate the other columns (e.g. GROUP_CONCAT) or use additional queries to get other related data.