SQL table join selection -

SQL table join selection -

i struggling select query i'm trying create bring together 2 tables has 1 many row ratio.

core table id | client_id | vehicle_code | risk | .... --------------------------------------------------------------------- 20 | 2 | c2e | there no risk | secondary table id | lteqr_id | period | milage | advanced_payments | subsequent_payments | total | .... --------------------------------------------------------------------------------------------------------------------- 10 | 20 | 1 year | 2000 | null | null | null | 11 | 20 | 2 years | 1000 | null | 23 | 16000.00 |

the issue having selecting rows based on columns beingness populated or not, have query select rows have subsequent_payments of null

select l.id ,l.client_id ,l.vehicle_code ,l.risk core_table l inner bring together secondary_table p on p.lteqr_id = l.id p.subsequent_payments null

which works fine subsequent_payments , other columns, when seek opposite is not null, still select row because in secondary table id 11 has value 23 subsequent_payments want select row if rows in secondary table not null , i'm struggling head around logic, help appreciated i'm open solution whether includes changing table designs don't mind.

you can subquery instead of join

select * core_table not exists ( select lteqr_id secondary_table lteqr_id = core_table.id , subsequent_payments null)

another way group by , utilize case: grouping records secondary_table, utilize case homecoming 1 or 0 depending on null status of subsequent_payments column, can filter having clause.

it's bit more complicated trivial solution above, perchance more performant alternative if have problems that.

sql