CODEWITHSUNDEEP
mysqlsqldatabase
Aniket Karne
Aniket Karne

Reputation: 45

Mysql query i can not solve

I have a situation here to get data(i.e. fname, lname and count(jobid)) from two different table comparing their jobid, deliverymanid, pickupmanid and employeeid from job and employee table and combine it in one row.

This is the job table


jobid      pickupmanid      deliverymanid
-----   ----------------  ------------------
  1                1              2
  2                2              2
  3                1              1

This is the employee table

employeeid        fname         lname
------------   -----------  -------------
    1               ABC           XYZ
    2               LMN           OPR

This is how i should get output 

employeeid   totalpickupjobs   totaldeliveryjobs       fname         lname
----------   ---------------   -----------------   -----------    -----------
    1               2               1                ABC            XYZ
    2               1               2                LMN            OPR

Upvotes: 0

Views: 56

Answers (3)

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT e.employeeid, 
(SELECT COUNT(*) FROM jobtable j
WHERE j.pickupmanid = e.employeeid) as totalpickupjobs,
(SELECT COUNT(*) FROM jobtable j
WHERE j.deliverymanid = e.employeeid) as totaldeliveryjobs,
e.fname, e.lname
FROM employeetable e

Go Sql Fiddle

Upvotes: 1

brenners1302
brenners1302

Reputation: 1478

Try this:

WITH x AS (SELECT 1 AS jobid,1 AS pickupmaid,  1 AS delivery_manid FROM dual UNION ALL
                  SELECT 2 AS jobid,2 AS pickupmaid,  2 AS delivery_manid FROM dual UNION ALL
                  SELECT 3 AS jobid,1 AS pickupmaid,  1 AS delivery_manid FROM dual ),
         y AS (SELECT 1 AS employeeid,'ABC' AS fname,  'XYZ' AS lname FROM dual UNION ALL
                   SELECT 2 AS employeeid,'LMN' AS fname,  'OPR' AS lname FROM dual )

SELECT y.employeeid as employee_id, 
        count(pickupmaid) as totalpickup, 
        count(delivery_manid) as totaldelivery, 
        y.fname as firstname, 
        y.lname as lastname
FROM y FULL OUTER JOIN x ON X.pickupmaid=y.employeeid group by y.employeeid, y.fname, y.lname;

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use mysql count ,inner join or group by:-

select *,count(job.deliverymanid) from Job inner 
join Pickupman on Job.pickupmanid=Pickupman.pickupmanid 
group by deliverymanid

Upvotes: 0

Related Questions