0

I've tried Googling this but couldn't find anything. When I run the query below, it outputs 4 identical rows. So I want to use the DISTINCT keyword to eliminate the duplicates. But I get an SQL error when I run the query. Remove the DISTINCT and it works fine.

SELECT DISTINCT list.`id`, * 
FROM `listings` list 
INNER JOIN `selections` sel
ON list.`id` = sel.`lid` 
WHERE 1 AND `activity` = 'running' 
AND ( 0 OR (sel.`parent` = 
'1') OR (sel.`parent` = '2') )
The Hawk
  • 1,496
  • 6
  • 26
  • 43
  • 1
    `distinct` like that applies to ALL of the fields you're selecting. it's not a per-field distinct. – Marc B Jul 30 '14 at 14:58
  • http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html – Emilio Gort Jul 30 '14 at 14:58
  • possible duplicate of [SQL - Select distinct but return all columns?](http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns) – BraedenP Jul 30 '14 at 14:58
  • In addition to the above. Your sql can be simplified. `WHERE activity = 'running' AND sel.parent in ('1','2')` – crthompson Jul 30 '14 at 14:58

1 Answers1

1
SELECT DISTINCT columns.you
              , actually.want
           FROM listings l
           JOIN selections s
             ON l.id = s.lid 
          WHERE activity = 'running' 
            AND s.parent IN(1,2)
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • 1
    Where do I put the DISTINCT then? SELECT DISTINCT id, title, address ? – The Hawk Jul 30 '14 at 15:04
  • 1
    @TheHawk DISTINCT is put first.. it applies to all of the fields.. you shouldn't select a column then * like you did in your query. thats not valid syntax for MySQL regardless. – John Ruddell Jul 30 '14 at 15:04
  • @JohnRuddell hit the nail. The misunderstanding is thinking that `distinct id, *` only affects `id`. It affects the `*` as well. – crthompson Jul 30 '14 at 15:08
  • @paqogomez `DISTINCT id, *` is not valid syntax in MySQL, so if the OP is using MySQL that wouldn't work. unless it was specified... something like `SELECT DISTINCT s.id, l.*` – John Ruddell Jul 30 '14 at 15:10
  • as is a lot of Strawberries comments ;) many are helpful though – John Ruddell Jul 30 '14 at 15:16