Retrieve additional rows if bit flag is true

Question

I have a large stored procedure that is used to return results for a dialog with many selections. I have a new criteria to get "extra" rows if a particular bit column is set to true. The current setup looks like this:

SELECT
  CustomerID,
  FirstName,
  LastName,
  ...
FROM HumongousQuery hq
LEFT JOIN (
    -- New Query Text
) newSubQuery nsq ON hq.CustomerID = nsq.CustomerID

I have the first half of the new query:

SELECT DISTINCT 
    c.CustomerID,
    pp.ProjectID,
    ep.ProductID
FROM Customers c
JOIN Evaluations e (NOLOCK)
    ON c.CustomerID = e.CustomerID
JOIN EvaluationProducts ep (NOLOCK)
    ON e.EvaluationID = ep.EvaluationID
JOIN ProjectProducts pp (NOLOCK)
    ON ep.ProductID = pp.ProductID
JOIN Projects p
    ON pp.ProjectID = p.ProjectID
WHERE 
    c.EmployeeID = @EmployeeID
    AND e.CurrentStepID = 5
    AND p.IsComplete = 0

The Projects table has a bit column, AllowIndirectCustomers, which tells me that this project can use additional customers when the value is true. As far as I can tell, the majority of the different SQL constructs are geared towards adding additional columns to the result set. I tried different permutations of the UNION command, with no luck. Normally, I would turn to a table-valued function, but I haven't been able to make it work with this scenerio.

This one has been a stumper for me. Any ideas?

How do you determine if the customer is to be included? Both when you allow indirect and when you don't. — zimdanen
– zimdanen, Commented Sep 20, 2013 at 20:55
The query above is for direct assignments to a specific employee. This one is used regardless of the bit flag. Basically, indirects are customers that are assigned to the employee, have the same product, but are in a different project. — Hector Sosa Jr
– Hector Sosa Jr, Commented Sep 20, 2013 at 20:59

zimdanen · Accepted Answer · 2013-09-20 21:06:17Z

2

So basically, you're looking to negate the need to match pp.ProjectID = p.ProjectID when the flag is set. You can do that right in the JOIN criteria:

JOIN Projects p
    ON pp.ProjectID = p.ProjectID OR p.AllowIndirectCustomers = 1

edited Sep 20, 2013 at 21:06

answered Sep 20, 2013 at 20:55

zimdanen

5,6367 gold badges47 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hector Sosa Jr Over a year ago

Holy Edge Case, Batman! Didn't know you could do that with an OR in the JOIN. Learnt something new today. :)

beercodebeer · Accepted Answer · 2013-09-20 21:04:59Z

0

Depending on the complexity of your tables, this might not work out too easily, but you could do a case statement on your bit column. Something like this:

select table1.id, table1.value, 
case table1.flag 
    when 1 then 
        table2.value
    else null
end as secondvalue
from table1
left join table2 on table1.id = table2.id

Here's a SQL Fiddle demo

answered Sep 20, 2013 at 21:04

beercodebeer

9906 silver badges5 bronze badges

1 Comment

zimdanen Over a year ago

I kinda wanna give you a +1 just for your username. But it sounds like the OP is looking to add rows rather than change the value of rows he already has.

Collectives™ on Stack Overflow

Retrieve additional rows if bit flag is true

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related