SQL query help needed.. - SQL Server Forums

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)

SQL query help needed..

Author

Topic

smokinmonkey
Starting Member

3 Posts

Posted - 2011-04-11 : 11:56:04

I am using Visual Studio 2008 to build an ASP.net / VB.net based website. I am connecting to an Access Database. I realise this might be a bit off topic in terms of the technology but it's just the SQL query I need help with.

Database tables:
Course(DegreeID, ModuleID, Level, Compulsory)
Modules(ModuleID, Module_Name, Points, Specification, Semester, State)
Enrolment(StudentID, ModuleID, Level, Enrolment_Year, Module_CATs)

Here is the original query I had written and used it to generate the modules available to a student depending on their course and level:

SELECT Modules.ModuleID, Modules.Module_Name, Modules.Points, Course.Compulsory
FROM Modules, Course
WHERE Course.DegreeID = 'variable' AND Course.Level = 'variable' AND Course.ModuleID = Modules.ModuleID

^This worked and displayed the information I wanted.

Now I want to expand this query to only display the above information for the modules the student is enrolled for. I wrote the query below but it isn't working:

SELECT Modules.ModuleID, Modules.Module_Name, Modules.Points, Course.Compulsory
FROM Modules, Course
WHERE Course.DegreeID = 'variable' AND Course.Level = 'variable' AND Course.ModuleID = Modules.ModuleID
LEFT JOIN Enrolment ON Enrolment.ModuleID = Course.ModuleID AND Enrolment.StudentID = 'variable'

I have tried other ways but cannot get it to work, the above query was my final effort and I'm surprised it didn't work!

I then want to display the above information for when a student is not enrolled in a module, I am assuming I just change the equals in this: 'LEFT JOIN Enrolment ON Enrolment.ModuleID = Course.ModuleID' to != for not equal? But as my above query doesn't work I can't test this idea.

Help please.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-11 : 12:28:59

Is this being done in SSMS or Access?

If you're doing it in Access, then I'm afraid you've come to the wrong place. If its in SSMS, then when you say your code "isn't working", do you mean the query fails or is it returning the wrong data?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 12:30:18

where Enrolment.StudentID is null

No, you're never too old to Yak'n'Roll if you're too young to die.

smokinmonkey
Starting Member

3 Posts

Posted - 2011-04-11 : 12:32:37

quote:
Originally posted by theboyholty

Is this being done in SSMS or Access?

If you're doing it in Access, then I'm afraid you've come to the wrong place. If its in SSMS, then when you say your code "isn't working", do you mean the query fails or is it returning the wrong data?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

I'm using Access, but surely the SQL query language will be universal across platforms?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 12:41:48

Add
where Enrolment.StudentID is null
to your query to see only rows where the student isn't enrolled.

OR

Add
where Enrolment.StudentID is NOT null
to your query to see only rows where the student IS enrolled.

If that isn't solving your problem then please be more specific.
Give us an idea about what "isn't working" means.
Give us table structure, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

smokinmonkey
Starting Member

3 Posts

Posted - 2011-04-11 : 12:41:58

I got this problem sorted. For some reason I forgot the , was a join!

I got rid and the left join, added enrolment to the comma joins and the where clause returns my results.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 12:45:41

Ah, now I see

Better you never use this messy old join syntax using just a comma...

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 18:27:22

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Without guessing from your narrative, I have done something like this for course offerings at a college. I found a nested sets model was the easiest way to give a student a list of options for his degree.

I am bothered by "compulsory" as an attribute; it is a value. Likewise, degree_id = 'variable' makes no sense for the same reason; this is an attribute value and not an identifier. I would assume that a degree has a name, like "MS-Computer Science" for its identifier.

I can kludge it, but your real problem seems to be a bad design.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources