T-sql 2012 self join issue

Transact-SQL
1

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?

2

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
3

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