sql - How to write a query that builds a string dynamically based on a 2 tables -

i have 2 tables: maintable , controltable.

i want write query builds string representing file path. file path built dynamically depending on query result between 2 tables.

main table has following columns: controlnumber customerid customerstatement

the control table has 1 column: controlnumber

i need write query checks if main table has controlnumber defined in control table. if there match, append \foldera filepath if no match, append \folderb

ending result this:

c:\customers\foldera or c:\customers\folderb

i suspect need use left join

how can that?

you're right want left join. combine case...when expression determine value:

select    *,    case      when control.controlnumber not null      '\foldera'      else '\folderb'    end filepath main  left join control on main.controlnumber = control.controlnumber 

it's not clear rest of path comes from; maybe it's static , want concatenate value case expression:

'c:\customers' + -- or concat() or || depending on sql dialect case when control.controlnumber not null '\foldera' else '\folderb' end filepath