Is it possible to JOIN with a var from a LEFT JOIN without destrorying rows?

Question

My code generates a large query. A simple version is

SELECT * FROM main_table as mt
JOIN user_data AS ud ON mt.user_id=ud.id 
LEFT JOIN ban_Status AS bs ON  ud.status_id=bs.id
JOIN AnotherTable ON bs.data=AnotherTable.id

NOTE: This code is untested.

When i remove the last join i get results. I can also change it to left join but that would be wrong. If ud.status is not null i would like a join as i always do when i do a select query from ban_Status. How do i fix this? must i write left join on every table if i left join the parent table? would that not give me side effects?

I am using sqlite ATM but will switch to tsql

Answer 1

Use the LEFT JOIN, but in your WHERE clause specify that either both ud.status_id is null and AnotherTable.id is null or neither is null.

SELECT * FROM main_table as mt
JOIN user_data AS ud ON mt.user_id=ud.id 
LEFT JOIN ban_Status AS bs ON  ud.status_id=bs.id
LEFT JOIN AnotherTable ON bs.data=AnotherTable.id
WHERE (ud.status_id is null and AnotherTable.id is null)
       or (ui.status_id is not null and AnotherTable.id is not null)

That will keep you from selecting any records that have a ban_Status but don't have the additional data from the other table.