Problem's with TOP or is my syntax incorrect? 1

[SalemGrafix]

I'm trying to use "TOP 1" to select the first record from a SQL Query, and it works fine, until I try to join it into another Query, and then it only returns NULL for everything.

Hopefully someone here can help on this one, I've been at this for days, and no luck thus far:

-----------------------
Working Code:

SELECT TOP 1 dbo.AdTerm.Code FirstTermCode, dbo.syStudent.SyStudentID, dbo.SyStudent.SSN SocialSecNo
FROM dbo.AdEnrollSched INNER JOIN
dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
ORDER BY dbo.AdTerm.StartDate

------------------------

But, as soon as I make it a subquery, it no longer works:

SELECT *
FROM
(SELECT TOP 1 dbo.AdTerm.Code FirstTermCode, dbo.syStudent.SyStudentID, dbo.SyStudent.SSN SocialSecNo
FROM dbo.AdEnrollSched INNER JOIN
dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
ORDER BY dbo.AdTerm.StartDate) FirstTerm

---------------------

Thanks for any help you can provide.

 

[SQLBill]

Why are you doing this?

You are selecting one row of data and then trying to select that row again. The first (outer) select is not needed and just a waste of time.

-SQLBill

 

[SalemGrafix]

Yes, I understand in the example above, that I'm selecting it twice, but the overall query is much broader than that.

To give a rundown, the database that we work with (not designed internally, unfortunately) does not have a way of tracking our students First Term, but by grabbing the term that is first after sorting them ascending by startdate of the term, I am able to get the first term.

This subquery will be linked to other information about the student (such as course, credits, program of study, etc.), and therefore it seemed logical to me, to make this a subquery that links to the main query.

I only posted it as an outer selection because even at this simplest of level, it will not return anything but NULLs, and was hoping there would be a quick easy answer to get around this. The full query is much larger and cumbersome, but if needed, can post that as well.

Thanks.

 

[zhavic]

Please, post your full query, because this one you post seems to be working.
I assume, that the problem is in other part of you query

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[SalemGrafix]

Here is the full query (including the subquery), which only returns NULL for the FirstTermCode. If the subquery is taken out, both run fine, it only appears to come up NULL when the two are joined.

Thanks for the help thus far, hopefully there is a solution.

---------
SELECT dbo.syStudent.SSN, dbo.syStudent.LastName, dbo.syStudent.FirstName, dbo.syStudent.MiddleName, dbo.syStudent.Suffix,
dbo.AdTerm.Code AS TermCode, dbo.syStudent.Addr1, dbo.syStudent.City, dbo.syStudent.State, dbo.syStudent.Zip, dbo.amSex.Descrip AS Gender,
dbo.amRace.Descrip AS Race, dbo.AdAttStat.Code AS AttStatus, dbo.AdProgram.Code AS ProgCode, dbo.AdProgram.Descrip AS ProgDesc,
dbo.AdProgramVersion.Code AS ProgVers, dbo.AdEnrollDegree.DateAwarded AS GradDate, dbo.syStudent.SyStudentID, dbo.SyCampus.Descrip,
dbo.syStudent.SyCampusID, dbo.AdEnroll.SyCampusID AS Expr1, dbo.syStudent.DOB, FirstTerm.FirstTermCode
FROM dbo.SyCampus FULL OUTER JOIN
dbo.syStudent LEFT OUTER JOIN
(SELECT TOP 1 dbo.AdTerm.Code FirstTermCode, dbo.syStudent.SyStudentID, dbo.SyStudent.SSN SocialSecNo, dbo.AdTerm.StartDate
FROM dbo.AdEnrollSched INNER JOIN
dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
ORDER BY dbo.AdTerm.StartDate) FirstTerm ON dbo.syStudent.SyStudentID = FirstTerm.SyStudentID LEFT OUTER JOIN
dbo.AdProgram RIGHT OUTER JOIN
dbo.AdProgramVersion ON dbo.AdProgram.AdProgramID = dbo.AdProgramVersion.AdProgramID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdProgramVersion.AdProgramVersionID = dbo.AdEnroll.adProgramVersionID LEFT OUTER JOIN
dbo.AdAttStat ON dbo.AdEnroll.adAttStatID = dbo.AdAttStat.AdAttStatID ON dbo.syStudent.SyStudentID = dbo.AdEnroll.SyStudentID LEFT OUTER JOIN
dbo.amRace ON dbo.syStudent.AmRaceID = dbo.amRace.AmRaceID LEFT OUTER JOIN
dbo.amSex ON dbo.syStudent.AmSexID = dbo.amSex.amSexID LEFT OUTER JOIN
dbo.AdEnrollDegree ON dbo.AdEnroll.AdEnrollID = dbo.AdEnrollDegree.AdEnrollID FULL OUTER JOIN
dbo.AdEnrollTerm ON dbo.AdEnroll.AdEnrollID = dbo.AdEnrollTerm.AdEnrollID FULL OUTER JOIN
dbo.AdTerm ON dbo.AdEnrollTerm.AdTermID = dbo.AdTerm.AdTermID ON dbo.SyCampus.SyCampusID = dbo.AdEnroll.SyCampusID

------

I somehow think that the bold is a little easier on the eyes then the italics was.

Again, thank you for any help you can provide on this.

 

[zhavic]

I am back from looking on your query with this results:

You said, that you get NULL values in 'FirstTermCode' column,
this is right, but I assume, that for one student this column contains some data
( and this is student with smallest 'dbo.AdTerm.StartDate' )


Subquery:
SELECT TOP 1 dbo.AdTerm.Code FirstTermCode,
dbo.syStudent.SyStudentID,
dbo.SyStudent.SSN SocialSecNo,
dbo.AdTerm.StartDate
FROM dbo.AdEnrollSched
INNER JOIN dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID
RIGHT OUTER JOIN dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID
RIGHT OUTER JOIN dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
ORDER BY dbo.AdTerm.StartDate

returns 'FirstTermCode' only for one student, with smallest 'dbo.AdTerm.StartDate' of all students,
and you join result of this subquery by 'ON dbo.syStudent.SyStudentID = FirstTerm.SyStudentID'
and this condition is true only for one student, for others it returns NULL,
because condition 'dbo.syStudent.SyStudentID = FirstTerm.SyStudentID' will be false

You must select the record with smallest 'dbo.AdTerm.StartDate' for each student

Try this, but don't now, what results it gives:

SELECT dbo.syStudent.SSN,
dbo.syStudent.LastName,
dbo.syStudent.FirstName,
dbo.syStudent.MiddleName,
dbo.syStudent.Suffix,
dbo.AdTerm.Code AS TermCode,
dbo.syStudent.Addr1,
dbo.syStudent.City,
dbo.syStudent.State,
dbo.syStudent.Zip,
dbo.amSex.Descrip AS Gender,
dbo.amRace.Descrip AS Race,
dbo.AdAttStat.Code AS AttStatus,
dbo.AdProgram.Code AS ProgCode,
dbo.AdProgram.Descrip AS ProgDesc,
dbo.AdProgramVersion.Code AS ProgVers,
dbo.AdEnrollDegree.DateAwarded AS GradDate,
dbo.syStudent.SyStudentID,
dbo.SyCampus.Descrip,
dbo.syStudent.SyCampusID,
dbo.AdEnroll.SyCampusID AS Expr1,
dbo.syStudent.DOB,

AdTerm_FirstTerm.FirstTermCode

FROM dbo.SyCampus
FULL OUTER JOIN dbo.syStudent

/* next 3 joins are may be the same as in your query ( but I not sure with LEFT and RIGHT joins ) */
/* they join all rows for each student, and in WHERE clause, there is filtered only one row with minimal 'dbo.AdTerm.StartDate' */
LEFT OUTER JOIN dbo.AdEnroll AdEnroll_FirstTerm ON AdEnroll_FirstTerm.SyStudentID = syStudent.SyStudentID
LEFT OUTER JOIN dbo.AdEnrollSched AdEnrollSched_FirstTerm ON AdEnrollSched_FirstTerm.AdEnrollID = AdEnroll_FirstTerm.AdEnrollID
INNER JOIN dbo.AdTerm AdTerm_FirstTerm ON AdTerm_FirstTerm.AdTermID = AdEnrollSched_FirstTerm.AdTermID

LEFT OUTER JOIN dbo.AdProgram
RIGHT OUTER JOIN dbo.AdProgramVersion ON dbo.AdProgram.AdProgramID = dbo.AdProgramVersion.AdProgramID
RIGHT OUTER JOIN dbo.AdEnroll ON dbo.AdProgramVersion.AdProgramVersionID = dbo.AdEnroll.adProgramVersionID
LEFT OUTER JOIN dbo.AdAttStat ON dbo.AdEnroll.adAttStatID = dbo.AdAttStat.AdAttStatID ON dbo.syStudent.SyStudentID = dbo.AdEnroll.SyStudentID
LEFT OUTER JOIN dbo.amRace ON dbo.syStudent.AmRaceID = dbo.amRace.AmRaceID
LEFT OUTER JOIN dbo.amSex ON dbo.syStudent.AmSexID = dbo.amSex.amSexID
LEFT OUTER JOIN dbo.AdEnrollDegree ON dbo.AdEnroll.AdEnrollID = dbo.AdEnrollDegree.AdEnrollID
FULL OUTER JOIN dbo.AdEnrollTerm ON dbo.AdEnroll.AdEnrollID = dbo.AdEnrollTerm.AdEnrollID
FULL OUTER JOIN dbo.AdTerm ON dbo.AdEnrollTerm.AdTermID = dbo.AdTerm.AdTermID ON dbo.SyCampus.SyCampusID = dbo.AdEnroll.SyCampusID

/* filter condiditon */
WHERE AdTerm_FirstTerm.FirstTermCode IN
( SELECT TOP 1 dbo.AdTerm.Code
FROM dbo.AdTerm
WHERE AdTerm.AdTermID = AdEnrollSched_FirstTerm.AdTermID
ORDER BY dbo.AdTerm.StartDate
)

-----------------------------
Post back, what results you get ( or errors )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[SalemGrafix]

Okay, did get errors trying to run the Query as stated, but you gave me all the information that I needed, as yes, you are correct, it does give me one student.

I originally thought that particular record was a fluke (as there is a lot of data conversion issues from the school's old system) but now it's clear that this truly is the first record in the database which has the earliest Term Startdate.

I believe that with the information that you gave me, I should be able to work thru this problem now (funny how problems are so tough until you see why they are problems).

Anyhow, thank you much for pointing me in the right direction, and I will post up here the completed Query that I come up with.

Thanks again.

 

[SalemGrafix]

Well, I finally got it done, and it works, thanks very much Zhavic for pointing me in the right direction.

Here is the completed query for anyone who might be interested in seeing how I went about accomplishing this.

The query takes about 20-30 seconds to run (depending on the term selected), but it does put a huge load on the SQL Server, I believe due to the way that the subqueries are wrote. I'm sure that there are more efficient ways of doing it, but since we only have to run this once per term, not a huge issue for us.

/* 10 Day Count Snapshot Per Term */
SELECT DISTINCT
dbo.syStudent.SyStudentID, dbo.syStudent.StuNum, dbo.AdTerm.Code AS TermCode, dbo.syStudent.SSN, dbo.syStudent.LastName,
dbo.syStudent.Suffix, dbo.syStudent.FirstName, dbo.syStudent.MiddleName, dbo.syStudent.DOB, dbo.amSex.Code AS GenderCode,
dbo.amSex.Descrip AS Gender, dbo.amRace.Code AS RaceCode, dbo.amRace.Descrip AS Race, dbo.syStudent.Addr1, dbo.syStudent.City,
dbo.syStudent.State, dbo.syStudent.Zip, dbo.SyCounty.Code AS CountyCode, dbo.SyCounty.Descrip AS County, dbo.AdProgram.Code AS ProgramCode,
dbo.AdProgram.Descrip AS Program, dbo.AdProgramVersion.Code AS ProgVerCode, dbo.AdProgramVersion.Descrip AS ProgVersion,
dbo.AdProgramVersion.SDF1 AS CIPCode, dbo.AdDegree.Code AS DegreeCode, dbo.AdDegree.Descrip AS Degree, dbo.AdAttStat.Code AS AttStatCode,
dbo.AdAttStat.Descrip AS AttStat, dbo.AdCourse.Descrip AS Course, dbo.AdEnrollSched.Credits AS CurCredits, dbo.AdEnrollTerm.CumCreditsAttempt,
dbo.AdEnrollTerm.CumCreditsEarned, dbo.AdEnrollTerm.CumGPA, dbo.AmHighSchool.Code AS HSCode, dbo.AmHighSchool.Descrip AS HighSchool,
dbo.syStudent.HsGradDate, FirstTerm.TermCode AS FirstTerm, FirstCreditTerm.TermCode AS FirstCreditTerm
FROM dbo.AdCourse FULL OUTER JOIN
dbo.AdAttStat RIGHT OUTER JOIN
dbo.AdTerm LEFT OUTER JOIN
dbo.AdEnrollTerm ON dbo.AdTerm.AdTermID = dbo.AdEnrollTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollTerm.AdEnrollID = dbo.AdEnroll.AdEnrollID ON dbo.AdAttStat.AdAttStatID = dbo.AdEnroll.adAttStatID LEFT OUTER JOIN
dbo.AdDegree RIGHT OUTER JOIN
dbo.AdProgramVersion ON dbo.AdDegree.AdDegreeID = dbo.AdProgramVersion.AdDegreeID LEFT OUTER JOIN
dbo.AdProgram ON dbo.AdProgramVersion.AdProgramID = dbo.AdProgram.AdProgramID ON
dbo.AdEnroll.adProgramVersionID = dbo.AdProgramVersion.AdProgramVersionID FULL OUTER JOIN
dbo.AdEnrollSched ON dbo.AdEnroll.AdEnrollID = dbo.AdEnrollSched.AdEnrollID AND
dbo.AdTerm.AdTermID = dbo.AdEnrollSched.AdTermID FULL OUTER JOIN
dbo.amSex RIGHT OUTER JOIN
dbo.syStudent LEFT OUTER JOIN
/* Subquery for First Term that is not Non-Credit For Student (First Term Attended) */
(SELECT DISTINCT TOP 100 PERCENT dbo.syStudent.SyStudentID, MIN(dbo.AdTerm.Code) AS TermCode
FROM dbo.AdEnrollSched INNER JOIN
dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
GROUP BY dbo.syStudent.SyStudentID
HAVING (NOT (MIN(dbo.AdTerm.Code) LIKE 'N%'))
ORDER BY dbo.syStudent.SyStudentID) FirstCreditTerm ON dbo.syStudent.SyStudentID = FirstCreditTerm.SyStudentID LEFT OUTER JOIN
/* Subquery for First Term regardless of Term (Credit or Non-Credit may be populated here) */
(SELECT DISTINCT TOP 100 PERCENT dbo.syStudent.SyStudentID, MIN(dbo.AdTerm.Code) AS TermCode
FROM dbo.AdEnrollSched INNER JOIN
dbo.AdTerm ON dbo.AdEnrollSched.AdTermID = dbo.AdTerm.AdTermID RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID
GROUP BY dbo.syStudent.SyStudentID
ORDER BY dbo.syStudent.SyStudentID) FirstTerm ON dbo.syStudent.SyStudentID = FirstTerm.SyStudentID LEFT OUTER JOIN
dbo.AmHighSchool ON dbo.syStudent.AmHighSchoolID = dbo.AmHighSchool.AMHighSchoolID LEFT OUTER JOIN
dbo.SyCounty ON dbo.syStudent.SyCountyID = dbo.SyCounty.SyCountyID LEFT OUTER JOIN
dbo.amRace ON dbo.syStudent.AmRaceID = dbo.amRace.AmRaceID ON dbo.amSex.amSexID = dbo.syStudent.AmSexID ON
dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentID ON dbo.AdCourse.AdCourseID = dbo.AdEnrollSched.AdCourseID
/* User selectable Term to run the query on */
WHERE (dbo.AdTerm.Code = 'SP-03')

----------------------------------------

Once again, thank you very much Zhavic, you definately pointed me in the right direction I needed to go.