473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Left/Outer Joins

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
Aug 14 '07 #1
1 1069
ck9663
2,878 Expert 2GB
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...remember 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
Aug 14 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
1
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
5
by: Dev | last post by:
Hello all, I need to do a left out join where a.field1 ilike %b.field2% But I can not figure out the exact syntax to using the ilike in the join? ----------
2
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since...
1
by: Eitan M | last post by:
Hello, I want to do select like this : select t1.col_2 from table_1 t1, table_2 t2 where t1.col_1 = t2.col_1 (+) The above is correct syntax for Oracle. What is the correct syntax for...
3
by: nico3334 | last post by:
I currently have a query that Joins 2 Tables (Table1 and Table2) using LEFT OUTER JOIN. Here is an example of that query: SELECT a.supply, a.state, b.cost FROM Table1 a LEFT...
1
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.