mysql DISTINCT keyword not working for multiple column join table

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
";

@JayBlanchard : I want to add DISTINCT to a.title and b.title — user3786234
– user3786234, Commented Aug 13, 2014 at 17:45
Well distinct doesn't do a lot if you select an id column. To filter out duplicates that appear both on the top and the bottom part of the union, replace union all with union. — Andomar
– Andomar, Commented Aug 13, 2014 at 17:46
@Andomar : done the changes , still issue remain. if i keep a.title and b.ttile on SELECt statemen its working , but when other coloumns alos mention on SELECt its not work. please advice — user3786234
– user3786234, Commented Aug 13, 2014 at 17:56
It would help if you explained "its not work". Provide example input, actual output and desired output. You can use sqlfiddle.com to set up an example. — Andomar
– Andomar, Commented Aug 13, 2014 at 18:04

Matt S · Accepted Answer · 2014-08-13 18:27:41Z

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.

edited Aug 13, 2014 at 18:27

answered Aug 13, 2014 at 17:49

Matt S

15.5k6 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3786234 Over a year ago

done the changes , still issue remain. if i keep a.title and b.ttile on SELECt statemen its working , but when other coloumns alos mention on SELECt its not work. please advice

Matt S Over a year ago

DISTINCT and UNION ALL check for dups across the whole row returned. Every column must have the same value for a row to not duplicate. If you want title and id to be unique, what do you expect for the 3rd column if it doesn't duplicate?

Collectives™ on Stack Overflow

mysql DISTINCT keyword not working for multiple column join table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related