Never seen this: LEFT OUTER JOIN ACTIVE STUDENTS ON STUDENTS.ID
Found this view that has multiple joins, but this one has me stumped:
FROM PERSON P WITH (NOLOCK) LEFT OUTER JOIN ACTIVE STUDENTS ON STUDENTS.ID = P.ID AND STUDENTS.[STATUS] = 1 AND STUDENTS.RNK = 1 LEFT OUTER JOIN ACTIVE APPLICANTS ON APPLICANTS.ID = P.ID AND APPLICANTS.[STATUS] = 0 AND APPLICANTS.RNK = 1 LEFT OUTER JOIN PERSON_ALT PA WITH (NOLOCK) ON PA.ID = P.ID AND PA.PERSON_ALT_ID_TYPES = 'RID'
--
There is not a table or view named ACTIVE STUDENTS or ACTIVE APPLICANTS.
In the view, there is not a select statement going to an alias of either as well. The view runs fine, but when I cut and paste the view into a new query, it gives errors.
Any help or idea on what they were doing would be greatly appreciated.
Is there a table called "ACTIVE"? What appears to be happening is the query is joining that table twice, and aliasing it so that one is called Students and the other is called Applicants.
You say "when I cut and past the view into a new query, it gives errors".
It would be nice to know which these errors are...
Thank you everyone. I did a right click on the view, design, and cut and pasted the query, which gave errors. I found the better way is to do a right click task, script as alter...this brought up ALL the query...which in the very beginning has: WITH ACTIVE (SELECT....
I would have thought opening the designer would have showed this as well.
Thanks everyone for the help!
