To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
This looks suspiciously like a homework question, but perhaps it isn't.
Let's start with the ORDER BY clause. The first column, EmpID, is
the primary key. Therefore all other ORDER BY columns are superfluous.
If this is homework, the required sort order was a trick question.
Besides, ORDER BY EmpID would scramble all the dupes.
Now, to tackle the SQL. Employees with the same first and last names
will necessarily be found on separate rows, and whenever more than one row
is required to satisfy a criterion, we should immediately think of grouping.
The following query identifies only those first and last name combinations
belonging to more than one employee. Note the GROUP BY and HAVING clauses.
select Lastname
, Firstname
from Employees
group
by Lastname
, Firstname
having count(*) > 1
In order to show the details of these employees, we simply use this
query as a derived table in the details query:
select EmpID
, Lastname
, Firstname
, DeptID
from Employees
where ( Lastname
, Firstname )
in ( select Lastname
, Firstname
from Employees
group
by Lastname
, Firstname
having count(*) > 1
)
order
by Lastname
, Firstname
, DeptID
This ORDER BY is somewhat more useful, eh.
P.S. If this wasn't a homework assignment, the above syntax will probably not
work in your particular database. Use the derived table in an INNER JOIN,
joining on Lastname and Firstname columns.
|
