CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

Retrieve additional rows if bit flag is true

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?

Upvotes: 1

Views: 154

Answers (2)

zimdanen
zimdanen

Reputation: 5626

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

Upvotes: 2

beercodebeer
beercodebeer

Reputation: 990

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

Upvotes: 0

Related Questions