t-sql 2012 self-join issue

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  January 25, 2017 at 3:59 pm

  #320950

  In t-sql 2012, I want to do a self join of a table to ifself.
  The value c1.attributeID = 1452 is the value that will always occur.
  The attributeID = 997 value occurs sometimes that is why I am trying
  to do a left join. I basically want to displays the columns called
  personID ,enrollmentID ,value  ,date from the c1 and c2 tables.

  Here is the t-sql 2012:
  select c1.personID  ,c1.enrollmentID ,c1.value ,c1.date     
      from  Test.dbo.CusStu c1
        where  c1.attributeID = 1452
     Left JOIN (select personID ,enrollmentID ,value  ,date
         from Test.dbo.CusStu   where  attributeID = 997)  AS c2
     on c2.personID=c1.personID

  I am getting the errors
  "Msg 156, Level 15, State 1, Line 9
  Incorrect syntax near the keyword 'Left'.
  Msg 156, Level 15, State 1, Line 15
  Incorrect syntax near the keyword 'AS'.".

  I do not know what is wrong with the self join.

  Thus could you modify the t-sql 2012 listed above
  so that the self join works for me?

• Andrew P

  SSCarpal Tunnel

  Points: 4649

  More actions

  January 25, 2017 at 5:06 pm

  #1924990

  wendy elizabeth - Wednesday, January 25, 2017 3:59 PM

  In t-sql 2012, I want to do a self join of a table to ifself.
  The value c1.attributeID = 1452 is the value that will always occur.
  The attributeID = 997 value occurs sometimes that is why I am trying
  to do a left join. I basically want to displays the columns called
  personID ,enrollmentID ,value  ,date from the c1 and c2 tables.

  Here is the t-sql 2012:
  select c1.personID  ,c1.enrollmentID ,c1.value ,c1.date     
      from  Test.dbo.CusStu c1
        where  c1.attributeID = 1452
     Left JOIN (select personID ,enrollmentID ,value  ,date
         from Test.dbo.CusStu   where  attributeID = 997)  AS c2
     on c2.personID=c1.personID

  I am getting the errors
  "Msg 156, Level 15, State 1, Line 9
  Incorrect syntax near the keyword 'Left'.
  Msg 156, Level 15, State 1, Line 15
  Incorrect syntax near the keyword 'AS'.".

  I do not know what is wrong with the self join.

  Thus could you modify the t-sql 2012 listed above
  so that the self join works for me?

  Your join statement should be in the FROM clause of the query, but you have it in the WHERE clause. If you would like to make faster progress, consider browsing for examples on Google. Here's a good forum thread on self-joins.

  Here's a revision to your query:

  select
    c1.personID as c1personID
    , c1.enrollmentID as c1_enrollmentID
    , c1.value as c1_value
    , c1.date as c1_date
    , c2.personID as c2personID
    , c2.enrollmentID as c2_enrollmentID
    , c2.value as c2_value
    , c2.date as c2_date
from Test.dbo.CusStu as c1
    left join Test.dbo.CusStu as c2
        on c1.personID = c2.personID
        and c2.attributeID = 997
where c1.attributeID = 1452