[SOLVED] Search result fm 2 tables, but only show a value that exists??

[jsonchan]

Hi,

 

I am a newbies of mySQL. Just try to use the following code to query employee's leave balance

 

SELECT
emp.hire_date, ent.entitle_id, ent.fiscal_year, ent.total_days, SUM(lev.no_of_days) AS `Used Leave`
FROM
employee AS emp , entitlement AS ent , leave_app AS lev
WHERE
emp.emp_id =  ent.emp_id 
AND emp.emp_id =  $_GET['employee_id'] 
AND emp.emp_id =  lev.emp_id 
AND ent.fiscal_year =  lev.fiscal_year
GROUP BY
emp.emp_id, ent.fiscal_year

 

There are three tables:

employee: the table consists of employee's personal info

entitlement: this table is keeping the entitlement of annual leave of all employees

leave_app: it holds all leave application info, such as the number of day of leave application

 

The problem is that the query only show the result when an employee has a row in the leave_app, otherwise it shows nothing.

 

But I would prefer the field "Used Leave" can show the "NULL" if the employee has never submitted any leave application.

 

Hire_Date  | Emp_id | Fiscal Year | Total Day | Used Leave

--------------------------------------------------------

YYYYMMDD |  10    |    2009      |    10        |  NULL

 

THANKS!

 

[onedumbcoder]

SELECT
emp.hire_date, ent.entitle_id, ent.fiscal_year, ent.total_days, IFNULL(SUM(lev.no_of_days), 'NULL') AS `Used Leave`
FROM
employee AS emp 
LEFT JOIN entitlement AS ent ON (emp.emp_id =  ent.emp_id)
LEFT JOIN leave_app AS lev ON(emp.emp_id =  lev.emp_id)
WHERE
emp.emp_id =  $_GET['employee_id']
AND ent.fiscal_year =  lev.fiscal_year
GROUP BY
emp.emp_id, ent.fiscal_year

[jsonchan]

Thanks. It works!!

 

Now I learned that "LEFT JOIN" can do the magic.