NOT DISTINCT query in mySQL

database, mysql, sql

Solution

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 = 8
       AND e1.superNiNumber = e2.ninumber
       AND e2.dno = 8
       and (select count(*) from employee e3
            where e3.superNiNumber = e1.superNiNumber) > 1;

Problem

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 supervisors are all held in the same table and are referenced by their ninumbers. The query I've been asked to build is: v. Find the NI Numbers, first and last names of employees and NI numbers of supervisors where the employees share that supervisor and both employees and supervisors work in department 8. You will refer to the employee relation twice was done in query vi of Practical 2. Your results should be presented in columns with the following titles ‘Employee NI number’, ‘First Name’, ‘Last Name’ and ‘Supervisor NI Number’. Therefore I created this query: ``` SELECT e1.ninumber, e1.fname, e1.minit, e1.lname, e1.address, e1.superNiNumber, COUNT(*) AS nrOfOccurences FROM employee AS e1, employee AS e2 WHERE e1.dno = 8 AND e1.superNiNumber = e2.ninumber AND e2.dno = 8 GROUP BY e1.superNiNumber HAVING COUNT(*) > 1 ``` I couldn't do a not distinct query to work out this part of the question - "where the employees share that supervisor". This query returns a grouping of the rows which in turn hides some of the rows I want to show. My question is: Is my query correct for the question and can I do a NON DISTINCT query in mySQL to get the database to return all of the fields instead of grouping them together. Reutrn results from my query ``` NInumber fname minit lname address supervisorNiNum number of occerences 666666601 Jill J Jarvis 6234 Lincoln, Antrim, UK 666666600 2 666666607 Gerald D Small 122 Ball Street, Letterkenny, IRL 666666602 3 666666604 Billie J King 556 WAshington, Antrim, UK 666666603 2 ``` Thanks.

Original source