t-sql 2012 want 'NOT in' or 'NOT EXISTS' to work

Question

t-sql 2012 want 'NOT in' or 'NOT EXISTS' to work

wendy elizabeth

SSCoach

Points: 16915
More actions
July 15, 2016 at 5:16 pm

#327869

In t-sql 2012, I have the following sql:
declare @gradYear int = 2017
SELECT distinct graduation.personID
into #gradPersonID
FROM [TEST].[dbo].[Graduation] graduation
where graduation.gradyear = @gradYear
SELECT distinct personID
FROM #gradPersonID graduation
where personID NOT in
(select distinct TranscriptCourse.personID from #gradPersonID graduation
join [TEST].[dbo].[TranscriptCourse] TranscriptCourse
on TranscriptCourse.personID = graduation.personID
where TranscriptCourse.grade between '09' and '12'
and @gradYear - 4 = TranscriptCourse.endYear)
DROP table #gradPersonID
I am joining to the graduation table so I can find students that are scheduled to graduate in a certain year. In this case the year is 2017.
What I am looking for is from the TranscriptCourse table by personID if there are no records where the endYear = 2014. Somehow I canot
seem to get the 'NOT IN' or 'NOT EXISTS' to work.
Thus would you show me how to change this sql so I canaccomplish my goal?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63436 More actions · Answer 1

CREATE TABLE scripts? INSERT scripts with dummy data? Lots easier to sort out with at least something to work with. Without that I can only hazard a guess.... something like:

SELECT personID

FROM #gradPerson g

WHERE NOT EXISTS (SELECT TC.PersonID

FROM TranscriptCourse tc

WHERE tc.PersonID = g.PersonID

AND tc.Grade BETWEEN '09' AND '12' --- that's really text???

AND @gradYear - 4 = tc.EndYear);

If you alias both tables (in the outer query and in the inner query), you can easily join them in the WHERE clause.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

would you show me the sql for the statement that you made:

'If you alias both tables (in the outer query and in the inner query), you can easily join them in the WHERE clause. "?

pietlinden SSC Guru Points: 63436 More actions · Answer 3

What? I don't understand your question.

You've been here way too long to post questions like you do. No data, no table structures, nothing. As such, all I can do is hazard a guess. You really ought to read Jeff's article on posting properly. It would help you ask better questions, which get better/tested answers.

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Also, you don't need the DISTINCT clause in any but the outermost SELECT statement. It's a really expensive operator, so use it only when you absolutely have to.

One query is aliased as TC, and the other is G. Then in the join in the subquery, the two are joined.

TC.Field = G.Field

pietlinden SSC Guru Points: 63436 More actions · Answer 4

I could rewrite the code this way...

SELECT personID

FROM #gradPerson AS g

WHERE NOT EXISTS (SELECT TC.PersonID

FROM TranscriptCourse AS tc

WHERE tc.PersonID = g.PersonID

Are you saying you don't understand what an alias is? I'm creating aliases to #gradPerson and TranscriptCourse so that I can join them in the correlated subquery... you just set the PersonID of the subquery (tc.PersonID) equal to the same field in the outer query (g.PersonID).