SQLTeam.com | Weblogs | Forums

T-sql 2012 self join issue

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?

join(s) should come immediately after the from statement, so in your case, the where statement for table alias c1 should be moved to after your join section

1 Like

You don't really have to do a join which would cause the table to be read twice.

SELECT c.personID,
    MAX(CASE WHEN c.attributeID = 1452 THEN c.enrollmentID END) AS enrollmentID,
    MAX(CASE WHEN c.attributeID = 1452 THEN c.value END) AS value,
    MAX(CASE WHEN c.attributeID = 1452 THEN c.date END) AS date,
    MAX(CASE WHEN c.attributeID =  997 THEN c.enrollmentID END) AS enrollmentID_997,
    MAX(CASE WHEN c.attributeID =  997 THEN c.value END) AS value_997,
    MAX(CASE WHEN c.attributeID =  997 THEN c.date END) AS date_997
FROM Test.dbo.CusStu c
WHERE c.attributeID IN (997, 1452)
GROUP BY c.personID
1 Like