2

I want to project the pid indexes for all products which have the same title, as I'm using the following as a sub query.

Product(pid, title)

SELECT p.title
FROM product p
group by title
HAVING ( COUNT(p.title) > 1 )

this outputs the duplicate titles fine, but how do I project the pid's instead?

AlasdairC
  • 190
  • 1
  • 1
  • 14

2 Answers2

5
SELECT p.pid, p.title
FROM product p 
     INNER JOIN 
     (SELECT p.title
      FROM product p
      GROUP BY title
      HAVING (COUNT(p.title) > 1)) t ON t.title = p.title

here is an example of it working with sqlfiddle:

http://sqlfiddle.com/#!3/25e77/1

Bassam Mehanni
  • 14,796
  • 2
  • 33
  • 41
  • sorry, this returns 38000 records, there are only 51 duplicate titles in the db. – AlasdairC Feb 08 '12 at 20:53
  • @AlasdairC how many titles does your sup query return? also, did you change anything in my query? – Bassam Mehanni Feb 08 '12 at 20:55
  • thought I didn't, but on second look, it totally does return the correct number of results, the pid of every title which appears more than once, which is exactly what I need, thanks. – AlasdairC Feb 08 '12 at 21:13
1

I think a self-join is the easiest answer on this case. Notice how we're testing for different PIDs but same titles:

SELECT p1.pid FROM products p1 JOIN products p2 
              ON p1.pid <> p2.pid AND p1.title = p2.title;

(I tested it here: http://sqlfiddle.com/#!2/c8b8d/18)

Hugo
  • 2,569
  • 1
  • 19
  • 18