iswariak Posted February 4, 2009 Share Posted February 4, 2009 Can anyone help me in optimising the below query?I have two tables.Table1: empdetailsFieldsempidfnamelnamehcmsupidemproleTable 2: leaveempidleavedateshiftEach employee has an HCM supervisor. In the empdetails, the hcmsupid is the hcm supervisor id.Now I want to get all the HCM supervisor and number of leaves taken by the employees under his supervision.Now, i have done in such a way by two queries. Can we optimise using only one queryquery = "select distinct hcmid from empdetails"rs.open query, myconn, 3, 3while not rs.eof sql = "SELECT count(*) " sql = sql & "FROM leave LEFT JOIN empdetails ON leave.empid = empdetails.empid " sql = sql & "WHERE empdetails.hcmid=" & hcmid 'processing stmts wend any ideas?thanks in advance Link to comment Share on other sites More sharing options...
Enthusiastic Student Posted February 4, 2009 Share Posted February 4, 2009 Hi IswariaHow about:'SELECT hcmsupid, COUNT(leavedate) FROM empdetails INNER JOIN leave USING empid ORDER BY hcmsupid'will that work?Enthusiastic Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.