Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Optimized query Expand / Collapse
Author
Message
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:

department

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

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 deptid FROM(
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Looks like homework...

http://efreedom.com/Question/1-5021993/Way-Make-SQL-Efficient
http://social.msdn.microsoft.com/Forums/en-CA/transactsql/thread/ee6e5643-2b7d-4bc2-a851-e6a407070d17
http://stackoverflow.com/questions/5021993/is-there-a-way-to-make-this-sql-more-efficient




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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 20,731, Visits: 32,495
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