return query will empty value - 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 2000 Forums

SQL Server Development (2000)

return query will empty value

Author

Topic

redfox26
Starting Member

2 Posts

Posted - 2006-03-31 : 00:19:21

hi

i have somes tables


USERS
======== 
USERID
NAME
PASSWORD

TEACHERS
=========
TEACHERID
USERID
COURSEID

COURSES
============
COURSEID
INITIALS
NAMECOURSE
SEMESTER
GROUP_NUMBER

ASSIGNMENTS
===============
ASSIGNMENTID
ASSIGNMENT_NUMBER
DESCRIPTION
SUBMISSION_START
SUBMISSION_END
COURSEID

DOCUMENTS
===========
DOCUMENTID 
ASSIGNMENTID 
COURSEID

with this query, i get every ASSIGNMENTS given by a teacher

SELECT *
FROM ASSIGNMENTS a
INNER JOIN COURSES c ON a.COURSEID = c.COURSEID
INNER JOIN TEACHERS t ON u.USERID = t.USERID
INNER JOIN USERS u ON t.COURSEID = c.COURSEID
WHERE u.USERID =444

i would like to know only assignment where there are not documentsid

any idea?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 00:31:50

"i would like to know only assignment where there are not documentsid"

select *
from   ASSIGNMENTS a left join DOCUMENTS d
       on d.ASSIGNMENTID = a.ASSIGNMENTID 
WHERE  DOCUMENTID is null

KH
Choice is an illusion, created between those with power, and those without.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-31 : 03:03:35

Or do you want to add that condition in your query?

Madhivanan

Failing to plan is Planning to fail

redfox26
Starting Member

2 Posts

Posted - 2006-03-31 : 07:22:53

i would like to add that condition in my query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-31 : 08:05:45

Try this

SELECT *
FROM ASSIGNMENTS a
INNER JOIN COURSES c ON a.COURSEID = c.COURSEID
INNER JOIN TEACHERS t ON u.USERID = t.USERID
INNER JOIN USERS u ON t.COURSEID = c.COURSEID
WHERE u.USERID =444 and not exists
(select DOCUMENTId from DOCUMENTS where ASSIGNMENTID =a.ASSIGNMENTID)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources