mysql join two tables with null value

Question

Im struggling to join two tables that might output some null value

here is my 1st table named: Leave_Type

id  name            days_entitled
1   Vacation leave   12
2   Sick leave       12
3   maternity leave  5
4   paternity leave  5

Leave Details

requester_id requester_name leave_type_id   days_consumed
1            John           1               1   
1            John           2               3
1            John           1               1

Id like to see the result for requester John

id  name            days_entitled   days_consumed
1   Vacation leave   12             2
2   Sick leave       12             3
3   maternity leave  5              0
4   paternity leave  5              0

Abhik Chakraborty · Accepted Answer · 2015-03-25 06:30:05Z

1

You can get the desired result as

select 
lt.*,
coalesce(x.days_consumed,0) as days_consumed
from leave_type lt 
left join ( 
  select leave_type_id, 
  sum(days_consumed) as days_consumed 
  from leave_details 
  where requester_id = 1 group by leave_type_id
)x on x.leave_type_id = lt.id

answered Mar 25, 2015 at 6:30

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2015-03-25 06:13:19Z

1

Try to using left join

select id,name,days_entitled, ifNull(days_consumed,0) from
Leave_Type left outer join Leave_Details on leave_type_id = id

answered Mar 25, 2015 at 6:13

Jens

69.6k15 gold badges107 silver badges133 bronze badges

3 Comments

freddy Over a year ago

i can get the same value if I filter the query (where requester_id=1)

Keval Over a year ago

what if John has two entry of same leave type like 2 ?

Jens Over a year ago

@Keval Then OP have to use group by and sum function. But it is not ask in the question as i can see.

freddy · Accepted Answer · 2015-03-25 06:26:37Z

0

Great.

select `requester`, tbl_hr_leave_type.id,name,`days_entitled`,sum(days) as Credit from
tbl_hr_leave_type left outer join tbl_hr_leave_form on tbl_hr_leave_form.`leave_details` = tbl_hr_leave_type.id and requester='411'
group by name
order by tbl_hr_leave_type.id

answered Mar 25, 2015 at 6:26

freddy

15510 bronze badges

Collectives™ on Stack Overflow

mysql join two tables with null value

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related