Cody Cook Cody Cook - 1 year ago 152
SQL Question

Compare Two Bits to One Bit

I have a query where I need to pull in all base and seasonal positions at a store, if the position is valid:

Select ojr.StoreNumber, ojr.Seasonal, ojr.JobType
FROM OJR ojr
Inner Join OJR_StoreInfo si on ojr.StoreNumber = si.Storenumber AND
CASE WHEN si.Seasonal-Barista = 1 Then 1 WHEN si.Base-Barista = 1
Then 0 ELSE NULL END = ojr.Seasonal


The table structures are like so:

OJR

ID StoreNumber Seasonal JobType
___________________________________
1 71001 0 Barista
2 71001 1 Barista
3 71002 0 Barista
4 71002 0 Sales


StoreInfo

StoreNumber Base-Barista Seasonal-Barista Base-Sales Seasonal-Sales
_______________________________________________________________________
71001 1 1 1 0
71002 0 1 1 1
71003 0 0 0 0


So the issue is that when I run the above query, ID 1 does not come in, because Storeinfo takes in both Seasonal AND base baristas, but the INNER JOIN determines that the I am looking for seasonal - because it hits the first WHEN and proceeds to "END" and never reaches the 2nd WHEN. Is there another way I can get around this? I think a subquery will be needed for the inner join, but I just can't figure it out yet.

Desired Output:

StoreNumber Seasonal JobType
__________________________________
71001 0 Barista
71001 1 Barista
71002 0 Sales


Thanks for any help!

Answer Source

You can re-formulate join criteria without case expression, like this:

SELECT
    ojr.StoreNumber
,   ojr.Seasonal
,   ojr.JobType
FROM OJR ojr
INNER JOIN OJR_StoreInfo si
   ON ojr.StoreNumber = si.Storenumber
 AND ((si.Seasonal-Barista = 1 AND ojr.Seasonal=1) OR (si.Base-Barista = 1 AND ojr.Seasonal=0))

The above condition eliminates the need to come up with a number that matches ojr.Seasonal, replacing it with two separate logical conditions.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download