m stuck in this simple query

[nabeelisnabeel]

EMPLOYEE table contains 'id' and 'name' columns.
ORDERS table contain 'emp_id'.

We have to select those employees who have more than 3 orders. I am unable to figure it out.

[davide++]

Hi all.

You didn't say what database you're using.
If you're usinig Oracle you can write the query:


SELECT CL.NAME
FROM EMPLOYEE CL,
(SELECT O.emp_id, COUNT(O.emp_id)
FROM ORDERS O
GROUP BY O.emp_id
HAVING COUNT(O.emp_id) > 3) O
WHERE CL.emp_id = O.emp_id


O hope this will help you.

[Alsvha]

As the query from davide is showing - HAVING is the keyword of interest here.

[nabeelisnabeel]

SQL Server 2000
SQL Server 2005

[nabeelisnabeel]

I wrote

SELECT
e.id
FROM
testemp e ,
(select emp_id, count(emp_id) from testorder group by emp_id having count(emp_id)>2) o
WHERE
e.id = o.emp_id


And faced this error

No column was specified for column 2 of 'o'.

[davide++]

Well.

I don't know SQLServer, so I don't know if it allows using virtual tables (the subquery in the FROM part of the query).
But reading the error message you got, I suppose that SQLServer expects a name instead of an expression.
So try this


SELECT e.id
FROM testemp e ,
(select emp_id, count(emp_id) AS CNT from testorder group by emp_id having count(emp_id)>2) o
WHERE e.id = o.emp_id


I hope it works.



Another solution


SELECT e.id
FROM testemp e
WHERE EXISTS
(SELECT emp_id, COUNT(emp_id)
FROM testorder O
WHERE O.emp_id = e.emp_id
GROUP BY O.emp_id
HAVING COUNT(O.emp_id) > 2)


This works in Oracle, and probably it works with SqlServer also.