A very simple SQL query help
-
Hi,
I need help making a very simple SQL Query.. I'm even feeling embarassed asking for help regarding this.. but anyways here it is..
Suppose a table Student with columns st_id and st_name
Another table Course with columns c_id and c_name
and a joining table student_course with columns st_id and c_id
Now the problem is:-
I want to get all students who are taking a particular course (say Mathematics, id=1), but not another particular course (say Physics, id=3)
Hoping for a quick reply...
Adnan
-
Adnan
Is this a homework question? Show us what you've come up with so far, and we'll try to point you in the right direction?
John
-
LOL
Actually I'm developing a software that will use a similar query at backend.. Real query is different,,but to understand the concept and illustrate my problem I've presented this simple example...
I read somewhere about Minus statement, but SQL Server doesn't support it, rather it has an Except statement,, on my SQL Server 2000 it does not work, may be because it is supported by SQL Server 2005.
I'm trying Not Exists statement, and apparently it is giving right results.. here is the query..
select student.* from student,stcourse where not exists (select * from stcourse where student.sid=stcourse.sid and stcourse.cid in (1,3)) and student.sid=stcourse.sid and stcourse.cid=2;
Above query is intended to show all students who are taking a course with ID=2, but not taking 1 or 3, it is working well.
Please tell me if it is the right approach? Is there some better solution or is there some problem with this query..
Thanks & Regards,
Adnan
-
Adnan
If it's working for you, then go with it. I tried rewriting using ANSI join syntax, and I got a bit bogged down... I'm sure someone else will be able to come up with an elegant solution more easily than I have been. The problem you have with your query is that you have hard-coded your course codes into it. Really you should join to the course table so that you refer in your query to "Mathematics" and Physics rather than their codes.
John
-
select
S.*
from student S
INNER join stcourse C
ON S.sid=C.sid
and C.cid=2
where not exists
( select *
from stcourse C1
where C1.sid = S.sid
and C1.cid in (1,3)) ;
Seems quit allright to me provided you have indexes for the sid-column and for the cid column in your tables.
It is offcourse according to your feeling, but IMO writing in join-syntax makes the query easier to read and comprehence because of diffenentiation of join- and filter predicates.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Select src.*
From dbo.Student src
Inner Join dbo.StudentCourse sc
On src.StudentID = sc.StudentID
Left Join dbo.StudentCourse sc2
On src.StudentID = sc2.StudentID
And sc2.CourseID in (2, 3)
Where sc.CourseID = 1
And sc2.StudentCourseID Is Null
-
just for fun, though completely illogical...
Select s.studentID
From Student s
Inner Join StudentCourse sc On s.StudentID = sc.StudentID
group by s.studentid
having sum(case when sc.courseid = 1 then 1 else 2 end) = 1
-
SELECT
s.st_id, s.st_name
FROM Student s
INNER JOIN (
SELECT st_id FROM student_course
WHERE c_id IN (1,3)
GROUP BY st_id
HAVING MAX(CASE WHEN c_id = 1 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN c_id = 3 THEN 1 ELSE 0 END) = 0
) c ON s.st_id = c.st_id
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply