March 17, 2009 at 11:37 am
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
March 17, 2009 at 11:47 am
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
March 17, 2009 at 11:53 am
Perfect.
I have to admit I am stupid.
Thanks a million.
Regards,
gova
March 17, 2009 at 12:05 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply