CODEWITHSUNDEEP
sqlsql-server-2008t-sqljoin
Justin
Justin

Reputation: 889

Joining to a table multiple times

I have three tables: Account, Management, and ManagementAccountLookup.

The Account table contains locations. The Management table contains records for every level in the business hierarchy. The Management table also contains a value (LevelID) that indicates which level in the hierarchy the record falls in. The ManagementAccountLookup table is the lookup table that connects them.

I'm having trouble writing a query to get all the accounts with two of their associated management records.

For example: an Account might have 5 or more management records associated to it, but I only care about two specific managements with a levelID of Brand or Region. Also, I only want an account to show up once in the result grid.

The result set should look something like this:

AccountID   Brand      Region
---------   --------   ------
account1    Wendys     East US
account2    McDonalds  West US

This seems like a simple problem, but I haven't been able to figure out exactly how to get that result. I have tried self-joins, subqueries, and everything else I could think of, but I can't seem to get the results into a single row.

Any help would be appreciated.

*EDIT: ManagementAccountLookup has two fields (AccountID, ManagementID). Those are the PKs of the two other tables.
Management has a column LevelID which is how you can tell if the record is a Brand, Region, District, etc...

The Brand and Region will be two separate rows in the Management table. I need the result grid to have them in the same row.

Upvotes: 2

Views: 194

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You need to add ManagementAccountLookup/Management combination twice to get informations in one row. I've put LevelID criteria directly in join to ease up on possible transition to left-joins if need arises.

select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
 inner join ManagementAccountLookup mal_brand
    on Account.AccountID = mal_brand.AccountID
 inner join Management m_brand
    on mal_brand.ManagementID = m_brand.ManagementID
   and m_brand.LevelID = @Insert_Management_Brand_Level_Here
 inner join ManagementAccountLookup mal_region
    on Account.AccountID = mal_region.AccountID
 inner join Management m_region
    on mal_region.ManagementID = m_region.ManagementID
   and m_region.LevelID = @Insert_Management_Region_Level_Here

EDIT: if you need to show all accounts you can use combination of left/inner join in parentheses:

select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
  left join 
  (
       ManagementAccountLookup mal_brand
       inner join Management m_brand
         on mal_brand.ManagementID = m_brand.ManagementID
        and m_brand.LevelID = @Insert_Management_Brand_Level_Here
 )
   on Account.AccountID = mal_brand.AccountID
 left join 
 (
       ManagementAccountLookup mal_region
       inner join Management m_region
          on mal_region.ManagementID = m_region.ManagementID
         and m_region.LevelID = @Insert_Management_Region_Level_Here
 )
    on Account.AccountID = mal_region.AccountID

To make it a bit more readable you might employ CTE:

; with mal_level as (
  select AccountID,
         m.LevelID,
         m.Name
    from ManagementAccountLookup mal
   inner join Management m
      on mal.ManagementID = m.ManagementID
)
select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
  left join mal_level m_brand
    on Account.AccountID = m_brand.AccountID
   and m_brand.LevelID = @Insert_Management_Brand_Level_Here
  left join mal_level m_region
    on Account.AccountID = m_region.AccountID
   and m_region.LevelID = @Insert_Management_Region_Level_Here

Or outer apply:

select Account.AccountID,
       b.Brand,
       r.Region
  from Account
 outer apply
 (
       select m_brand.Name Brand
         from ManagementAccountLookup mal_brand
        inner join Management m_brand
           on mal_brand.ManagementID = m_brand.ManagementID
          and m_brand.LevelID = @Insert_Management_Brand_Level_Here
        where mal_brand.AccountID = Account.AccountID
 ) b
 outer apply
 (
       select m_region.Name Region
         from ManagementAccountLookup mal_region
        inner join Management m_region
           on mal_region.ManagementID = m_region.ManagementID
          and m_region.LevelID = @Insert_Management_Region_Level_Here
        where mal_region.AccountID = Account.AccountID
 ) r

Upvotes: 2

Related Questions