Sub Query - 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

Other Forums

Other Topics

Sub Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-18 : 09:22:41

Ravindrareddy writes "I have two table Assignment and Assignment_Record.Assignment has three field AssignmentId,CourseId and Description. Assignment_Record has three fields AssignmentId,StudentId and Answer.
For exmple
1. Table : Assignment
AssignmentId CourseId Decription
101 IT501 What is OS?
102 IT502 What is RUP?
103 IT501 What is Kernel?

2. Table : Assignment_Record
AssignmentId StudentId Answer
101 2002001 Operating System
101 2002002 Rational Unified Process

See carefully, in the above table both the students have not submitted the '103' (AssignmentId).
Suppose i am student(2002001), i logged through some interface, i have to see what are the assignments, i have not submmitted for a particular subject? Here the user providing the Inputs
StudentId = 2002001
CourseId = IT501
Output to
What is Kernel? i want .""

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-18 : 09:37:01

Generally, when posting to message boards, rather than giving use prose about your table desing, you should just provide the CREATE TABLE statements that are relevant to your problem so that someone who wants to help can just cut & paste rather than have to translate your personal psuedo-code into SQL....

Your expected result doesn't jive with your requirements as it seems as though studen 2002001 hasn't answered the 'What is RUP?' and the 'What is Kernal?' assignments, so your rowset should contain both.

Assuming your expected results where wrong, there are several ways to do this. The best is probably a correlated subquery using the not exists condition in your where clause. Something like....


select
    a.description
from
    assignment a
where
    not exists (
        select 1
        from
            assignment_record
        where
            studentid = 200201 and
            assignmentid = a.assignmentid)

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources