Is there a way to concat two columns from a table and join three tables

February 1, 2023

I am trying to join three tables, but in one of the tables I need to concat two columns for being able to do the left join.

select * from 
 order_dupe_check_cleaned dcc left join AAPEN_SORLIN asr
on dcc.INTERNAL_ORDER_ID = asr.VLCODE 
left join order_dupe_atributes oda on dcc.INTERNAL_ORDER_ID = oda.INTERNAL_ORDER_ID 
and oda.product_id = --** Here I want to insert the concat statement**(select concat_ws(VLDEPT,VLSTYL)as product_id2 pi from AAPEN_SORLIN) 
where dcc.order_id like '73901124'
;

I have added a comment where I want to insert the concat function however not sure how to do it.

>Solution :

with strings you can concat simply by using || .

Because you’ve already joined the fields via left join AAPEN_SORLIN asr you should be able to acesss them directly.

See how you go 🙂

select 
    * 
from 
 order_dupe_check_cleaned dcc 
 left join AAPEN_SORLIN asr
on dcc.INTERNAL_ORDER_ID = asr.VLCODE 
left join order_dupe_atributes oda on dcc.INTERNAL_ORDER_ID = oda.INTERNAL_ORDER_ID 
and oda.product_id = VLDEPT||VLSTYL

--** Here I want to insert the concat statement**(select concat_ws(VLDEPT,VLSTYL)as product_id2 pi from AAPEN_SORLIN) 
where dcc.order_id like '73901124'