Query help entry shifts joining transaction shifts

Ignition
1

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
2

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!

3

I am just asking if I can use case like this in the join section

4

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

image

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?

5

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
6

Thanks, that should be more efficient for sure.