However some of the codes store in Customer table do not exist in Codes table.
I need to let Codes table left join Customer table, this is the first version SQL:
select * from Codes a left outer join Customer b on a.CodeID in (b.PrimaryCode)
That did not give me the correct outcome as query above miss out the values in OtherCodes column, so I try this:
select * from Codes a left outer join Customer b on a.CodeID in (CAST(b.PrimaryCode as varchar) + ',' + b.OtherCodes)
I try to concat the PrimaryCode + OtherCodes to have a clause like this: IN(‘1,2,3,4,5’), but that does not seems right, and I get the error message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘23,24,26,27’ to data type int.
Ok, now I know that is not right doing that, there are few solutions provided in internet (search from Google of course!). The final solution would be using LIKE keyword, then I have this which work perfect:
select * from Codes a left outer join Customer b on ',' + Cast(b.PrimaryCode as varchar) + ',' + b.OtherCodes + ',' LIKE '%,' + Cast(a.CodeID as varchar) + ',%'
The result I want:
Hints to search in Google: Comma separated values in IN Clause