storing where clause in table to use in vb string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

storing where clause in table to use in vb string

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2008-12-01 : 16:45:26

I'm building a sql string in a vbScript by concantinating a selectField and a whereField stored in a table. My goal is to identify the youngest students in a family starting with the third student up to max students. I have the following:


SELECT TOP ((SELECT COUNT(stuId) AS studentsInFamily FROM tblStudents WHERE (yrRecId = 1) AND (famId = 5)) - 2) stuId
FROM tblStudents AS tblStudents_1 
WHERE (yrRecId = 1) AND (famId = 5) 
ORDER BY stuDOB DESC

This returns the exact list that I need. However, my problem is that I'm iterating through the stuIds in a family in my code and checking to see if the currentStuId in my loop is one of the stuIds in this list. So I need the above query to actually be a subquery in a WHERE clause where I compare the currentStuId with one of the values returned by the query above. Confused? I'm amazed if you follow that.

I don't know if this helps but I've added the above query to a WHERE clause and set it equal to a family that has only 3 students. It works perfectly, I can identify the youngest student if there are 3 students in the family. However the query breaks when I use it on a family with more than 3 students because the subquery returns more than one row.

the following works with 3 students or less but not more than 3...I'm doing a vb Replace function to replace the #TOKENS# with current values.


SELECT COUNT(stuId) AS intRecordCount FROM tblStudents
WHERE (yrRecId = #YEAR#) AND (famId = #FAM#) AND (stuId = #STUD#) AND 
((SELECT TOP ((SELECT COUNT(stuId) AS studentsInFamily FROM tblStudents WHERE (yrRecId = #YEAR#) AND (famId = #FAM#)) - 2) stuId FROM tblStudents AS tblStudents_1 WHERE (yrRecId = #YEAR#) AND (famId = #FAM#) ORDER BY stuDOB DESC) = #STUD#)

In short is there a reverse IN() function where I can say currentStuId IsInList(subquery)?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 17:05:03

Maybe this:

SELECT COUNT(stuId) AS intRecordCount 
FROM tblStudents
WHERE (yrRecId = #YEAR#)
  AND (famId = #FAM#)
  AND (stuId = #STUD#)
  AND #STUD# in ((SELECT TOP ((SELECT COUNT(stuId) 
                               FROM tblStudents 
                               WHERE (yrRecId = #YEAR#)
                                 AND (famId = #FAM#)) - 2) 
                  stuId 
                  FROM tblStudents AS tblStudents_1 
                  WHERE (yrRecId = #YEAR#)
                    AND (famId = #FAM#) 
                  ORDER BY stuDOB DESC))

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

lcsgeek
Starting Member

38 Posts

Posted - 2008-12-02 : 08:26:46

Hey Webfred,
Much appreciated your idea worked like a charm and you made it simple.

Thanks much

Darin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 08:59:32

Can't the new ROW_NUMBER() function help?

SELECT	*,
	ROW_NUMBER() OVER (PARTITION BY famID ORDER BY yrRecID) AS seqID
FROM	tblStudents

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources