Unable to set a temp variable in join statement

1

Hello - I'm hoping someone can help me here. I'm needing to write a query that joins 3 tables, however if the information isn't on my w table it should still show that data from my c table. Here is what I have so far

select *
from [frisk].[fmcsa_fcwd].[conviction] c
left join [frisk].[fmcsa_fcwd].[withdrawal_conviction_link] l
on c.driver_license_number = l.driver_license_number
and c.conviction_id = l.conviction_id
left join [frisk].[fmcsa_fcwd].[withdrawal] w
on c.driver_license_number = w.driver_license_number
and c.conviction_acd_code = w.withdrawal_acd_code
where l.conviction_id is null
and w.withdrawal_jurisdiction_code ='FH'
and c.conviction_category in('VOOSO', 'RRGHC','MAJOR','SERIOUS')
and c.conviction_date_posted >= convert(datetime, '2021-01-01 23:59:00',0)

Any help would be greatly appreciated.

2

I'm fairly certain the problem lies in jurisdiction needs to be FH, however if that information doesn't exist at all - I still need the other data

3

You need to move the WHERE condition for the "w" table to the LEFT JOIN instead of in the WHERE:


select *
from [frisk].[fmcsa_fcwd].[conviction] c
left join [frisk].[fmcsa_fcwd].[withdrawal_conviction_link] l
on c.driver_license_number = l.driver_license_number
and c.conviction_id = l.conviction_id
left join [frisk].[fmcsa_fcwd].[withdrawal] w
on c.driver_license_number = w.driver_license_number
and c.conviction_acd_code = w.withdrawal_acd_code
and w.withdrawal_jurisdiction_code ='FH' --<<--
where l.conviction_id is null
and c.conviction_category in('VOOSO', 'RRGHC','MAJOR','SERIOUS')
and c.conviction_date_posted >= convert(datetime, '2021-01-01 23:59:00',0)
4

Thank you so much