some people are manually entering A, B, C, D
A and B are to match shift 1
C and D are to match shift 2
When I wrote my query, I had
and table1.shiftID=table2.shift
Now though, can I say :
join left ...........and table1.shiftID = case
when table2.shift=1 then '1'
when table2.shift=2 then '2'
when table2.shift=A then '1'
when table2.shift=B then '1'
when table2.shift=C then '2'
when table2.shift=D then '2' end
Is there a question hidden in there somewhere?
You’d need to show the structure of both tables and what output you want from your query for us to help.
Then ask a question!
I am just asking if I can use case like this in the join section
I got it to work indirectly.
relatively
select *
from Counts
Left join Data on
Counts.Shift_ID= case
when Data.Shift= 1 then 1
when Data.Shift= 2 then 2
when Data.Shift= 2601 then 1
when Data.Shift= 2602 then 1
when Data.Shift= 2603 then 2
when Data.Shift= 2604 then 2 end

I used 2601 for A, 26 letters in the alphabet
used 2604 for D
It seems that in this way I can map a column to various values of another column.
I have a subquery before this to trim down the size of the first table before the join.
Is there a better way? Or maybe an alternate way with different tradeoffs?
I’m not exactly sure what you are doing here, but you can definitely shorten the case a bit.
select *
from Counts
Left join Data on
Counts.Shift_ID= case
when Data.Shift in (1,2601,2602) then 1
when Data.Shift in (2,2603,2604) then 2
end
2 Likes
Thanks, that should be more efficient for sure.