CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
user4884237
user4884237

Reputation:

SQL Query WITH CASE in WHERE clause

I have to return rows for an entity.

An entity can have multiple office addresses and one of these office addresses can be a headquarters.

I have a table where I display all the entities but I can only display one address for an entity.

So I have to select at least one address

  1. Address with a headquarters

or

  1. Select Top 1 Address

    SELECT *
FROM Entity
LEFT JOIN ......
WHERE
   [INSERT  CODE TO GET ADDRESS]

Upvotes: 0

Views: 77

Answers (2)

Dimitri
Dimitri

Reputation: 7013

Without knowing your schema, I'll try to give you a general idea about how you could achieve what you want.

CREATE TABLE #tempAddress (
    ID int not null,
    CompanyID int not null,
    Address varchar(50),
    IsHQ bit not null
)

CREATE TABLE #tempCompany (
    ID int not null,
    Name varchar(50)
)

INSERT INTO #tempAddress (ID, CompanyID, Address, IsHQ)
SELECT 1, 1, 'Address 1', 1
UNION ALL
SELECT 2, 1, 'Address 2', 0
UNION ALL
SELECT 3, 1, 'Address 3', 0
UNION ALL
SELECT 4, 1, 'Address 4', 0
UNION ALL
SELECT 5, 2, 'Address 5', 1
UNION ALL
SELECT 6, 2, 'Address 6', 0
UNION ALL
SELECT 7, 3, 'Address 7', 0

INSERT INTO #tempCompany (ID, Name)
SELECT 1, 'Company 1'
UNION ALL
SELECT 2, 'Company 2'
UNION ALL
SELECT 3, 'Company 3'


;with addr as (
    select *, ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY CASE WHEN IsHQ=1 THEN 0 ELSE ID END) AS RowNum
    from #tempAddress
)
SELECT * 
FROM #tempCompany C
    LEFT JOIN addr A ON C.ID = A.CompanyID AND A.RowNum=1

DROP Table #tempAddress
DROP Table #tempCompany

RESULT:

ID  Name             AddressID  CompanyID   Address      IsHQ   RowNum
1   Company 1        1          1           Address 1    1      1
2   Company 2        5          2           Address 5    1      1
3   Company 3        7          3           Address 7    0      1

In the example Company 1 and 2 have headquarters, Company 3 does not.

As you see, I use PARTITION BY to number the addresses, showing the headquarter address on top of any other address that might be present. Then join the result with the company table and only select the top 1 row.

And here's Sql Fiddle, courtesy of Juan Carlos Oropeza.

Upvotes: 2

Chains
Chains

Reputation: 13157

An alternate approach to using ROWNUMBER()...

Declare @TheOnesIWantTable Table (AddressID int, CompanyID int)

--One Address Per Company With HQ

INSERT @TheOnesIWantTable (AddressID,CompanyID)
Select [AddressID], [CompanyID] From [AddressTable] Where [HQ] = 1

--One Address Per Company NOT With HQ

INSERT @TheOnesIWantTable (AddressID,CompanyID)
Select MAX(AddressID), CompanyID From [AddressTable] WHERE [CompanyID] NOT IN
   (Select [CompanyID] From [AddressTable] Where [HQ] = 1)

@TheOnesIWantTable now holds all (and only) the address ID's that you want in your final result set. Now you can use it in a JOIN:

SELECT
   C.*,
   A.*
FROM
   CompanyTable C

   INNER JOIN AddressTable A
   ON C.CompanyID = A.CompanyID

   INNER JOIN @TheOnesIWantTable T
   ON A.AddressID = T.AddressID

Upvotes: 0

Related Questions