Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Optimized query Expand / Collapse
Posted Saturday, June 18, 2011 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 6:15 AM
Points: 1, Visits: 8
Consider the following tables:


deptid (type:INT)
deptname (type: TEXT)
hours (type:INT)
active (type:BIT)

empid (type:INT)
empname (type: TEXT)
deptid (type: INT)
designation (type: TEXT)
salary (type: INT)
Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname..

I write like this..

SELECT e1.empname, d.deptname from employee AS e1
FULL JOIN department AS d on e1.deptid = d.deptid
WHERE e1.deptid IN(
SELECT e2.deptid, COUNT(e2.empid)
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
ORDER BY empname;

How would you improve on this?
Post #1127784
Posted Saturday, June 18, 2011 1:26 PM



Group: General Forum Members
Last Login: Friday, April 1, 2016 1:37 AM
Points: 1,778, Visits: 8,370
Looks like homework...

Clear Sky SQL
My Blog
Post #1127791
Posted Saturday, June 18, 2011 2:21 PM



Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 23,522, Visits: 37,756
Why are you using a full join instead of an inner join?

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1127794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse