Need Some Serious Help With Self Join Issue

Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from micr

Solution 1:

Here's what I got from the blogpost

• Let's say you want to join 2 times on dbo.circt_cstdn i.e. you want something like

           owner       tech
  rowA     a.nm        b.nm
  ...
  

• Instead of getting the values into 2 columns you get it into 2 rows (2 for each row above) and add an additional column to say which row is for which column. Note that row 1.1 and row 1.2 have the same data (except for the name and for columns)

           name   for
  row1.1   nm     owner
  row1.2   nm     tech
  ...
  

• Then you pivot on max of name column for owner and tech. Note - the max function is just to trick the PIVOT (which requires some aggregate function), you could use any aggregate function that returns the same value if there is only one record owner tech row1 nm nm ...

Now if we do this for your query

1. Create a table d, like this one

    i
    1
    2
   

2. Cross join the first part of your query with this

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d  
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, d.i
   

3. Now let's use the row for owner if D.i is 1, and the tech if D.i is 2

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d 
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END  
   

4. Now add the nm column. To get the name you join circt_cstdn with circt if it's an owner row (d.i = 1), and with dvc if it's a tech row (d.i = 2). Note - I tried a shortcut here by putting this in the join condition. If it doesn't work try the blog post way (do a join on circt.circt_cstdn_user_id OR dvc.circt_cstdn_user_id, and then use the WHERE clause to filter out)

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END as PersonType,
        circt_cstdn_nm
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d 
        INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id = 
             CASE
                  WHEN d.i = 1 THEN circt.circt_cstdn_user_id
                  WHEN d.i = 2 THEN dvc.circt_cstdn_user_id
             END
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END,
        circt_cstdn_nm
   

5. Create a view using that and create the index

   create VIEW vw_lookup_test_imed
   WITH SCHEMABINDING 
   AS
       <<query above>>  
   GO
   
   spell to create INDEX
   

6. Now you PIVOT to convert the PersonType column to Owner and Tech columns

   SELECT 
        count_all, 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        [Owner], 
        [Tech] 
   FROM 
   ( 
        SELECT 
             count_all, 
             awc_txt, 
             city_nm, 
             str_nm, 
             stru_no, 
             dvc.circt_nm, 
             data_orgtn_yr,
             PersonType,
             circt_cstdn_nm
        FROM dbo.vw_lookup_test_imed WITH (NOEXPAND) 
   ) src 
   PIVOT 
   ( 
        MAX(circt_cstdn_nm) 
        FOR PersonType IN ([Owner], [Tech]) 
   ) pvt 
   

If there are syntax errors (there's bound to be lots cause I don't have access to a database right now) let me know.

Solution 2:

I think this JOIN syntax is horrible and originated from MS Access. ugh. I recommend you use:

select
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm [owner],
t.circt_cstdn_nm [tech],
dvc.circt_nm,
data_orgtn_yr 

-- HERE
from dbo.dvc
join dbo.circt on (dvc.circt_nm = circt.circt_nm) 
join dbo.circt_cstdn o on (circt.circt_cstdn_user_id = o.circt_cstdn_user_id)
join dbo.circt_cstdn t on (dvc.circt_cstdn_user_id = t.circt_cstdn_user_id)

group by
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm,
t.circt_cstdn_nm,
dvc.circt_nm,
data_orgtn_yr 

This syntax is cleaner, more comprehensible and is recognized in SQL Server, Firebird, Oracle, MySQL and much others. Now you can see better the relations between "tables". When you join the same "table" two or more times, you need to alias each one. On one line "circt_cstdn" is aliased as "o". On other line, "circt_cstdn" is aliased as "t".

I recommend use of LEFT JOIN or INNER JOIN instead JOIN.