Sql Join Group Account

Question

This query is based on a transaction table.

SELECT Ecode,COUNT(*) AS recvd FROM Transaction_tbl WHERE Locid=5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY Ecode ORDER BY Ecode DESC 

during the execution of this request, I exit like this:

 Ecode recvd E003 24 E001 2 

I have another table, such as Master Employee, the corresponding name is Employee is save, how can I join this table to the table of employee workshops.

I want to get out of this

 Ecode Ename Recvd E003 jas 24 E001 deepu 14 

Answer 1

Try

 SELECT e.Ecode, e.Ename, q.recvd FROM Employee e JOIN ( SELECT Ecode,COUNT(*) Recvd FROM Transaction_tbl WHERE Locid = 5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY Ecode ) q ON e.Ecode = q.Ecode ORDER BY e.Ecode DESC 

or simply

 SELECT e.Ecode, e.Ename, COUNT(*) Recvd FROM Employee e JOIN Transaction_tbl t ON e.Ecode = t.Ecode WHERE Locid = 5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY e.Ecode DESC 

Here is a SQLFiddle demonstration.

Answer 2

I think you can use the INNER JOIN query

 SELECT a.Ecode,COUNT(a.*) AS recvd, b.name FROM Transaction_tbl a INNER JOIN employee_master b ON a.id = b.id WHERE a.Locid=5 AND a.dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY a.Ecode ORDER BY a.Ecode DESC 

Just change ON a.id = b.id to the actual name of the column that has the relation and table name.

Answer 3

 SELECT t.Ecode, e.name, COUNT(*) AS recvd FROM Transaction_tbl JOIN Employee e ON e.Ecode = t.Ecode WHERE Locid=5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY t.Ecode, e.name ORDER BY Ecode DESC 

Answer 4

Your EmployeeMaster or Transaction_tbl table must have a foriegn key to do what you want. If you do, it just helps.

If the foreign key is in EmployeeMaster

  SELECT t.Ecode,e.Ename,t.COUNT(*) AS recvd FROM Transaction_tbl as t INNER JOIN EmployeeMaster as e ON t.Ecode = e.Ecode WHERE t.Locid=5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY t.Ecode ORDER BY t.Ecode DESC 

If it is in Transaction_tbl

  SELECT t.Ecode,e.Ename,t.COUNT(*) AS recvd FROM Transaction_tbl as t INNER JOIN EmployeeMaster as e ON t.EmasterForeignKey = e.EmasterPrimaryKey WHERE t.Locid=5 AND dtime BETWEEN '5/1/2013 00:00:00' AND '6/10/2013 23:59:59' GROUP BY t.Ecode ORDER BY t.Ecode DESC 

I hope this helps