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.
| Author |
Topic |
|
andretru2
Starting Member
2 Posts |
 Posted - 2011-02-28 : 15:16:46
|
| I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065Here is my query:select distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, course_name, course_number from students inner join storedgrades on students.id=storedgrades.studentidwhere (students.grade_level >'9' and students.grade_level <= '12') and (course_number = all('6710', '6711', '1032', '1035') and course_number=Any('3061','3065'))order by last_name, first_name, grade_level, course_numberThe problem is that I don't get any results. But when I change the query to OR instead of AND I can see that students took the courses required (6710, 6711, 1032, 1035 and any 3061, 3065). What am I doing wrong? Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
andretru2
Starting Member
2 Posts |
Posted - 2011-02-28 : 15:33:49
|
| Oracle 10g. Am I in the wrong forum? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-02-28 : 15:58:04
|
ALL and ANY(or SOME) are valid in SQL Server, but the way you are using them is not correct.Both ALL and ANY require a subquery, not a list of delimited values.Example from SQL Server Books Online:IF @NumberOfDays >= ALL ( SELECT DaysToManufacture FROM Sales.SalesOrderDetail JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID WHERE SalesOrderID = @OrderID )PRINT 'All items for this order can be manufactured in specified number of days or less.'ELSE PRINT 'Some items for this order cannot be manufactured in specified number of days or less.' ; CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
=ALL or =ANY query problem