问题描述:

I have two parameters that I want to filter by, If competitorID is null or zero then I should filter the set against all competitor ids in another table else I should filter against competitorID.

I have written the following but it is wrong as it filters against all the competitors.

SELECT

*

FROM DashboardDataCache AS d

WHERE ISNULL ( @CompetitorID , 0 ) = 0

OR d.CompetitorID IN (

SELECT

Value

FROM dbo.SessionFilters

WHERE SessionID = @SessionID

AND field IN ( 'competitor' , 'article_competitor' )) ;

网友答案:

Try changing your query as follows:

SELECT
   *
FROM DashboardDataCache AS d
      -- Filter by ID if @CompetitorID is not null
WHERE (@CompetitorID IS NOT NULL AND d.CompetitorID = @CompetitorID)
      -- Otherwise, filter using SELECT
OR    (@CompetitorID IS NULL AND  d.CompetitorID IN (
          SELECT Value
          FROM dbo.SessionFilters
          WHERE SessionID = @SessionID
           AND field IN ( 'competitor' , 'article_competitor' )));
网友答案:

Try it like that:

SELECT
       *
  FROM DashboardDataCache AS d
  WHERE (ISNULL ( @CompetitorID , 0 ) = 0 and d.CompetitorID = @CompetitorID)
     OR (ISNULL ( @CompetitorID , 0 ) != 0
         AND d.CompetitorID IN ( 
                            SELECT
                                   Value
                              FROM dbo.SessionFilters
                              WHERE SessionID = @SessionID
                                AND field IN ( 'competitor' , 'article_competitor' ))) ;
相关阅读:
Top