Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select first (Minimum) avilable course Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 11:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 4:07 PM
Points: 458, Visits: 88
DECLARE @Courses TABLE
(
CourseID INT,
Course VARCHAR(256)
)

INSERT @Courses
SELECT 1, 'Course1' UNION
SELECT 2, 'Course2' UNION
SELECT 3, 'Course3' UNION
SELECT 4, 'Course4'


DECLARE @EnRollment TABLE
(
CourseID INT,
Student VARCHAR(256)
)

INSERT @EnRollment
SELECT 1, 'Student1' UNION
SELECT 1, 'Student2' UNION
SELECT 1, 'Student4' UNION
SELECT 1, 'Student5' UNION
SELECT 1, 'Student6' UNION
SELECT 2, 'Student1' UNION
SELECT 2, 'Student4' UNION
SELECT 2, 'Student5' UNION
SELECT 3, 'Student1' UNION
SELECT 3, 'Student5' UNION
SELECT 3, 'Student6' UNION
SELECT 4, 'Student1'

/*

I need the minimum eligiple course for a student if I pass student

Example

Student1 is enrolled in all courses I need a - null
Student2 Enroled 1 next 2 is not enrolled I need - 2
Student3 did not enroll any so I need - 1
Student5 Enrolled in 1, 2, 3, So I need - 4

*/

DECLARE @NewCourse INT

DECLARE @Student VARCHAR(256)
SELECT @Student = 'Student5'

SELECT @NewCourse = SQLQuery


SELECT @NewCourse -- This should return 4


Regards,
gova
Post #677736
Posted Tuesday, March 17, 2009 11:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Try this:

select @NewCourse = 
(select min(Courses.CourseID)
from @Courses Courses
left outer join @Enrollment Enrollment
on Courses.CourseID = Enrollment.CourseID
and Student = @Student
where Enrollment.CourseID is null);

See if that'll do what you need.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #677749
Posted Tuesday, March 17, 2009 11:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 4:07 PM
Points: 458, Visits: 88
Perfect.

I have to admit I am stupid.

Thanks a million.


Regards,
gova
Post #677754
Posted Tuesday, March 17, 2009 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Not stupid. Just have stuff to learn about SQL. Who doesn't?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #677768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse