need to find an elegant way to retrieve data

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 2008 Forums

Transact-SQL (2008)

need to find an elegant way to retrieve data

Author

Topic

amitbarkai
Starting Member

19 Posts

Posted - 2011-09-11 : 03:20:16

Hi All

i am trying to find elegant solution for my problem
i have 3 tables
1.students
2.courses
3.StodentCourses (Many to Many)

i want to find all students
that take 'Math' AND 'Physics' ...

select S.Name,C.Name
from Students S
join StodentCourses SC ON S.ID = SC.StudentID
join Courses ON C.CourseID = C.ID
where A.Name like 'Math' or
A.Name like 'Physics'
order by S.Name

will partially take the data we need , since students that take
only Math or Physics will also be included

while using comparison we are referencing to the same cell
and it is quite confusing since we want to get students that takes
Math AND Physics ( using and on this query will give us no results)
is there any way to get this data with only one query ?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 11:48:36

[code]
select S.Name
from Students S
join StodentCourses SC ON S.ID = SC.StudentID
join Courses ON C.CourseID = C.ID
where C.Name IN ('Math', 'Physics')
GROUP BY S.Name
HAVING COUNT(DISTINCT C.Name)=2
order by S.Name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amitbarkai
Starting Member

19 Posts

Posted - 2011-09-12 : 02:53:50

thanks for the help
BUT
lets take another step forward
i want to have the ability to choose few courses
and create a dynamic query

maybe student take take Math Physics Literature
and that the Math final grade is more than 80

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 03:01:42

then you need to pass the courses as a comma seperated list in a parameter and use it like


DECLARE @Courselist varchar(8000)
SET @CourseList='Math,Physics,Biology,English'

select S.Name 
from Students S
join StodentCourses SC ON S.ID = SC.StudentID
join Courses ON C.CourseID = C.ID
where ',' + @CourseList + ',' LIKE '%,' + C.Name + ',%'
GROUP BY S.Name
HAVING COUNT(DISTINCT C.Name)=LEN(@CourseList)-LEN(REPLACE(@CourseList,',',''))+1
AND SUM(CASE WHEN C.Name = 'Math' THEN C.Grade ELSE 0 END)>80 
order by S.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources