1

Exmaple:

[empid    date      bookid]
----------
1        5/6/2004   8

2        5/6/2004   8

1        5/7/2004   8

1        5/8/2004   6

3        5/8/2004   8

2        5/8/2004   7

In this table,I need to get empid 1 as output..since it has bookid 8 more than once..

thanks in advance..

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
anand
  • 13
  • 1
  • 4
  • Are you sure you don't want to group by `empid`, `bookid`, AND `date`? The meaning of date could be important. – vol7ron Oct 06 '10 at 01:14

2 Answers2

4

You can use:

SELECT DISTINCT id
FROM table
GROUP BY empid, bookid
HAVING COUNT(*) > 1

But it will give you duplicates. If, for example, you have 1-8,1-8,1-9,1-9 you will get 1,1 as output because empid 1 has duplicate bookid's for two distinct bookid values. You will need to use SELECT DISTINCT to filter out the duplicate empid.

Alin Purcaru
  • 43,655
  • 12
  • 77
  • 90
  • Alin Purcaru, Thanks for your ans..query works correct if i add distinct.. SELECT DISTINCT id FROM table GROUP BY empid, bookid HAVING COUNT(*) > 1 – anand Oct 06 '10 at 01:09
0
SELECT empid
from table
group by empid
having Count(distinct bookid) > 1
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • you select empid that has more than one distinct bookid, the question asks for empid with the same bookid more than once. your query would output 1 and 2, not just 1. – Alin Purcaru Oct 05 '10 at 09:20