CODEWITHSUNDEEP
mysqlsql
Julio Cesar
Julio Cesar

Reputation: 27

Getting NULL in a SQL left JOIN as result

create or replace view vw_fill_thresholds as
SELECT 
fill_thresholds.id,
fill_thresholds.tenant_id,
waypoint_locations.name,
material_type.description as material, 
fill_thresholds.can_size,
fill_thresholds.threshold,
units.description as unit,
fill_thresholds.post_threshold_cost as ptc,
ptu_units.description as ptu_unit
FROM fill_thresholds
left join fill_locations on fill_thresholds.fill_id = fill_locations.id 
and fill_thresholds.deleted = 0
left join units on  fill_locations.unit_id = units.unit_id
left join waypoint_locations on `waypoint_locations`.`id` = `fill_locations`.`waypoint_id`
left join `material_type` on `material_type`.`id` = `fill_locations`.`material`
left join `units` `ptu_units` on  fill_thresholds.post_threshold_unit_id = units.unit_id

I need to select the same column as units. So, In my fill_thresholds table I have two fields that contain an id from the units table: example 2 and 3.

Now, in my units table I have the following fields: id and description. example data: 1 km, 2 miles, 3 yards ,etc. so in my view according to the numbers I have in fill_thresholds it should show miles and yards. Right now I'm getting NULLs which I don't understand why.

Upvotes: 0

Views: 38

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6854

Your last join should be as per below-

left join `units` `ptu_units` on  fill_thresholds.post_threshold_unit_id = ptu_units.unit_id

Right now you are joining your units table based on 2 columns first with fill_locations.unit_id = units.unit_id and 2nd with fill_thresholds.post_threshold_unit_id = units.unit_id, so not getting corresponding rows ans showing null.

Upvotes: 1

SergeyAn
SergeyAn

Reputation: 764

The problem with your query is that you're trying to guess what is wrong analysing the whole query. It is too complicated so try to join your tables one by one.

1)fill_thresholds LEFT JOIN fill_locations

2)fill_thresholds LEFT JOIN fill_locations LEFT JOIN units

3)fill_thresholds LEFT JOIN fill_locations LEFT JOIN units LEFT JOIN waypoint_locations and so on

This way you can find where is the problem

Upvotes: 1

Related Questions