CODEWITHSUNDEEP
sql-serverjoinleft-join
Manuel Pena
Manuel Pena

Reputation: 125

SQL Server : multipart identifier subquery

Is possible to use column org_level.level_name in the second JOIN? I have tried to merge the second JOIN but I got the next message :

The multi-part indentifier could not be found

Code:

SELECT
    base.*, 
    org_level.level_id
FROM
    dbo.raw_data base 
LEFT JOIN 
    (SELECT DISTINCT 
         level_id AS level_name, 
         CASE 
            WHEN level_id = 'state' THEN 1 
            WHEN level_id = 'region' THEN 2 
            WHEN level_id = 'division' THEN 3 
            ELSE '' 
         END AS level_id 
     FROM            
         dbo.raw_data) AS org_level ON org_level.level_name = base.level_id 
LEFT JOIN 
    (SELECT 
         loc.state_id,
         CASE 
            WHEN org_level.level_name = 1 THEN 1 
            ELSE 0 
         END AS Number
     FROM
         dbo.locations AS loc) AS geo ON geo.state_id = base.Region_ID

Upvotes: 0

Views: 62

Answers (1)

Pred
Pred

Reputation: 9042

In the second join, org_level does not exist, the scope of that subquery is limited to that specific subquery. Anything what is outside of that scope (say the parenthesis) is not visible to the query engine while processing the things within that scope.

You can move the CASE..WHEN construct to the main level SELECT list or you can use OUTER APPLY instead of LEFT JOIN.

The whole query does not make much sense. The first join uses the same table and just calculates some values using that CASE..WHEN construct and the result is joined back using the very same column.

Isn't this what you really want?

SELECT
    base.*, -- Stars only visible at night, list the columns you really need.
    CASE 
      WHEN level_id = 'state' THEN 1 
      WHEN level_id = 'region' THEN 2 
      WHEN level_id = 'division' THEN 3 
      ELSE '' 
    END AS level_id, -- Do you really want 2 columns named level_id in the result?
    CASE
      WHEN geo.state_id IS NOT NULL AND level_id = 1 THEN 1
      ELSE 0
    END AS Number
FROM
    dbo.raw_data base 
    LEFT JOIN dbo.locations AS geo
      ON geo.state_id = base.Region_ID

Upvotes: 2

Related Questions