Make multiple SQL into one Query

Question

I have a table in SQL Server 2005 as shown below

DECLARE @Promotions TABLE (PromoCode VARCHAR(10),
                          DeptID VARCHAR(10),
                          VerticalID VARCHAR(10),
                          BuildingID VARCHAR(10)
                          primary key (DeptID, VerticalID, BuildingID)
                        )

INSERT INTO @Promotions VALUES ('P1_20%','101','501','1001')
INSERT INTO @Promotions VALUES ('P2_10%','101','501','All')
INSERT INTO @Promotions VALUES ('P3_5%','101','All','All')
INSERT INTO @Promotions VALUES ('P1_50%','111','606','1002')

We need to find out promotion code value based on the matching values in DeptID, VerticalID and BuildingID columns. If there is not match with these 3 columns, look for a match with the input value for DeptID, VerticalID and a default value ("All") for BuildingID. Still if there is no result found, look for a match by considering input DeptID, default value for VerticalID ("All") and default value for BuildingID ("All").

The following query works fine – however it is using a 3 part approach. Can we achieve this in a single SQL query?

EXISTING CODE

DECLARE @PromoCode VARCHAR(10)

--Part 1
SELECT @PromoCode = PromoCode
FROM @Promotions
WHERE DeptID = @inputDeptID
  AND VerticalID = @inputVerticalID
  AND BuildingID = @inputBuildingID

--Part 2
IF @PromoCode IS NULL 
BEGIN
    SELECT @PromoCode = PromoCode
    FROM @Promotions
    WHERE DeptID = @inputDeptID
      AND VerticalID = @inputVerticalID
      AND BuildingID = 'All'
END

--Part 3
IF @PromoCode IS NULL 
BEGIN
    SELECT @PromoCode = PromoCode
    FROM @Promotions
    WHERE DeptID = @inputDeptID
      AND VerticalID = 'All'
      AND BuildingID = 'All'
END

--Result
SELECT @PromoCode

Testing

DECLARE @inputDeptID VARCHAR(10)
DECLARE @inputVerticalID VARCHAR(10)
DECLARE @inputBuildingID VARCHAR(10)

SET @inputDeptID = '101'
SET @inputVerticalID = '501'
SET @inputBuildingID = '3003'

Expected Result

P2_10%

Gordon Linoff · Accepted Answer · 2014-08-18 21:55:53Z

3

You can approach this as a "prioritization". That is, look for all possible matches and then choose the one that is best, using order by and top:

set @PromoCode = (SELECT top 1 PromoCode
                  FROM @Promotions
                  WHERE DeptID = @inputDeptID AND
                        (VerticalID = @inputVerticalID OR VerticalId = 'ALL') AND
                        (BuildingID = @inputBuildingID OR BuildingID = 'ALL')
                  ORDER BY (case when Building = 'ALL' then 1 else 0 end),
                           (case when VerticalId = 'ALL' then 1 else 0 end)
                 );

There are other ways to express this. If performance is an issue and you have indexes on the columns, then a union all approach with order by may work better than ors in the where clause.

answered Aug 18, 2014 at 21:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

LCJ Over a year ago

Thanks. But I am not able to understand how it works. Can you please elaborate what is the relevance of 1 and 0 in the case statement and what is the logic behind this?

Gordon Linoff Over a year ago

The order by statement is ordering the rows being returned, with exact matches at the top of the list and defaults at the bottom. run the select query without the top 1 and you will see what happens for different inputs.

Collectives™ on Stack Overflow

Make multiple SQL into one Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related