[FIXED] How to Join the table in phpmyadmin

Issue

This is two query that i need to combine:

first query:

SELECT emp.name, count(rej.employee_ic) as 'rejected leave'
FROM employee as emp
LEFT OUTER JOIN rejectedleave as rej
ON emp.ic_no = rej.employee_ic
GROUP BY emp.`ic_no`

Second query:

SELECT emp.name, count(app.employee_ic) as 'approved leave'
FROM employee as emp
LEFT OUTER JOIN approvedleave as app
ON emp.ic_no = app.employee_ic
GROUP BY emp.`ic_no`

The output:

first query: first query output image

second query: second query output image

i want to combine this two table into one table. please help me to solve this problem, appreciate your help.

Solution

Is this you need? It find the count of approved and rejected leaves for each available employee in separate subqueries and then joins it with the employee table to show the count of rejected and approved leaves for each employee in one row.

Use coalesce(col,0) to return 0 if the count if null. Coalesce returns first non null value from the given list of arguments.

select
    t1.*,
    coalesce(t2.approvedleave,0) approvedleave
    coalesce(t3.rejectedleave,0) rejectedleave
from employee t1
left join (
    select employee_ic, count(*) approvedleave
    from approvedleave
    group by employee_ic
) t2 on t1.ic_no = t2.employee_ic
left join ( 
    select employee_ic, count(*) rejectedleave
    from rejectedleave
    group by employee_ic
) t3 on t1.ic_no = t3.employee_ic;

Read about coalesce here:

• http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php

Answered By - Gurwinder Singh