Left/Outer Joins

[Mickey001]

Greetings all,

I need some help in writing and understanding a sql query for the following:

Here is a simplified scenario:

2 Tables:

First Table Name
=============
Failed Subjects

Fields
=========
Name of Student
Date of Test
Name of Subject(failed)


Second Table Name
===============
Master Subjects

Fields
=========
Name of Student
Subject enrolled

I need to have a report that shows -
Date of Test
Name of Student
Subjects Enrolled
Name of Failed Subject

If the student has passed the test for a subject, it should appear as 'Passed!' in the "Name of Failed Subject Field"

Thanks in advance!
Mickey

[ck9663]

Greetings all,

I need some help in writing and understanding a sql query for the following:

Here is a simplified scenario:

2 Tables:

First Table Name
=============
Failed Subjects

Fields
=========
Name of Student
Date of Test
Name of Subject(failed)


Second Table Name
===============
Master Subjects

Fields
=========
Name of Student
Subject enrolled

I need to have a report that shows -
Date of Test
Name of Student
Subjects Enrolled
Name of Failed Subject

If the student has passed the test for a subject, it should appear as 'Passed!' in the "Name of Failed Subject Field"

Thanks in advance!
Mickey

it would be easier if you have a key on each table (ie STUDNUM) to relate these three tables...rememb er that two students may have the same name.

select [Failed Subjects].[Date of Test],
[Master Subjects].[Name of Student], [Master Subjects].[Subject enrolled], isnull([Failed Subjects].[Name of Subject(failed)],'PASSED')
from [Master Subjects]
left join [Failed Subjects] on [Master Subjects].[STUDNUM] = [Failed Subjects] .STUDNUM