Not Distinct Query In Mysql

I have been asked to create a query for this on a simple employee database columns include: ninumber - first name - last name - address - SuperVisorNiNumber The employees and supe

Solution 1:

In your result table column description, I see no minit, address and number of occurrences. Therefore I would simplify your select to:

SELECT e1.ninumber,
       e1.fname,
       e1.lname,
       e1.superNiNumber,
FROM   employee AS e1,
       employee AS e2
WHERE  e1.dno = 8AND e1.superNiNumber = e2.ninumber
       AND e2.dno = 8and (select count(*) from employee e3
            where e3.superNiNumber = e1.superNiNumber) > 1;

Solution 2:

The accepted answer is quite slow in performance. After a bit of searching I managed to produce a faster running equivalent:

SELECT e1.ninumber,
       e1.fname,
       e1.lname,
       e1.superNiNumber
FROM   employee AS e1, (SELECT superNiNumber, 
                        COUNT(*) AS count 
                        FROM employee 
                        GROUPBY superNiNumber 
                        HAVING count > 1) AS e2
WHERE  e1.superNiNumber = e2.superNiNumber

Credit: http://www.programmingforums.org/thread14669.html