CODEWITHSUNDEEP
mysqljoin
sanu
sanu

Reputation: 1068

Select record from table with not in other table

I am stuck in query I have a table like this

budget_details

id  budget_id  expenditure_head_id  budget
1   1          1                    1233
2   1          2                    333
3   1          3                    567
4   2          1                    343
5   2          2                    343
6   2          3                    6767
7   2          4                    557

expenditure_heads

id name
1  abc
2  xyz
3  qwe
4  uvw

I want to get all the expenditure_heads from budget_details that even if not in budget_details like here budget_id=1 does not contain expenditure_head_id 4 but I want to select that to with 0 or null displaying

I tried this but it not displaying expenditure_head_id 4

select `expenditure_heads`.`name`, `budget_details`.`budget`, `budget_details`.`id` from 
`budget_details` 
right join `expenditure_heads` on `budget_details`.`expenditure_head_id` = `expenditure_heads`.`id` 
 where `budget_details`.`budget_id` = 1"

Upvotes: 1

Views: 28

Answers (1)

olibiaz
olibiaz

Reputation: 2595

The where avoid you to get the missing row you need. The left join is done on the ON statement, so this query should work for you:

 SELECT EH.name, BD.budget, BD.id FROM expenditure_heads EH 
 LEFT JOIN budget_details BD 
     ON (BD.expenditure_head_id = EH.id AND BD.budget_id = 1)

Upvotes: 1

Related Questions