# Query help entry shifts joining transaction shifts

**URL:** <https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684>\
**Category:** Ignition\
**Created:** [March 1, 2022, 7:18pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684 "2022-03-01T19:18:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [March 1, 2022, 7:18pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/1 "2022-03-01T19:18:37Z")

</div>

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

```auto
and table1.shiftID=table2.shift

```

Now though, can I say :

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 1, 2022, 8:36pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/2 "2022-03-01T20:36:52Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [March 2, 2022, 1:27pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/3 "2022-03-02T13:27:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [March 2, 2022, 1:51pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/4 "2022-03-02T13:51:04Z")

</div>

I got it to work indirectly.

relatively

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/0/401bf975d24f72ce11e5b64f3e463f314b4c9d8a.png)

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?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 2, 2022, 2:09pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/5 "2022-03-02T14:09:06Z")

</div>

I’m not exactly sure what you are doing here, but you can definitely shorten the case a bit.

```auto
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

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [March 2, 2022, 2:18pm UTC](https://forum.inductiveautomation.com/t/query-help-entry-shifts-joining-transaction-shifts/56684/6 "2022-03-02T14:18:09Z")

</div>

Thanks, that should be more efficient for sure.