Joining 3 tables with 1 to many records for a distinct recordset

I want to return a set of unique records from Three tables where one table holds distinct Student information, and another holds 1 to many records for each Student according to the number of classes being taken, and the third holds the number of classes being given per term:

Table 1: Studid (PK), Department, Location, Major

Table 2: Studid (PK), Classid (PK), Class-Name, Room-Number

Table 3: Classid (PK). Class-name



So, there are 9 total classes available this term, listed as 1 thru 9, from table 3. Student “Joe” is taking classes 1, 3, 4 and 9, Student “Mat” is taking classes 2, 3, 5, and 8. So table 2 has four records for Joe, Identified by his Stuid + Classid, and table 2 has four records for Mat, identified by the same key fields. I want to return a single record for each student, including a “flag” for all classes taken and not taken by identifying the classes taken (Table 2 records) and then filling in the recordset fields with either a Y (because table 2 had a entry for that class) or a N if the table didn’t have a record for that class/studid:



Name StuID Cl1 Cl2 Cl3 Cl4 Cl5 Cl6 Cl7 Cl8 Cl9

Joe 11121 -Y- -N- -Y- -Y- -N- -N- -N- -N- -Y-

Mat 12213 -N- -Y- -Y- -N- -Y- -N- -N- -Y- -N-



There are hundreds of students. I have done this by looping through the Table 1 recordset return, and for each student, running another set of queries to return table 2 values, then parsing into a list with Y or N as compared to table 3, but it takes WAY TO LONG TO RUN, and times out for any great number of records.



IS there a way to run just one Joined or Nested type of query to optimize this without having to loop through queries within queries? It’s an Oracle 9i DB displayed in Cold Fusion.



Thanks

Okay, I have created a database to match your structure. Post some sample data I can add to the database and post how you expect (want) the data to look like when it is returned by the query.

TABLE 1
Studid____Dept____Location___Major
IS11100___IS________Jax_____IS/IT
IS12210___IS________Jax_____IS/IT
CS39006__CS________Jax_____HR
CS29000__CS________Jax_____HR

TABLE 2
Stuid___Classid____Class-Name____Room-Number
IS11100___2______Sys Analysis_____232c
IS11100___4______Database Tech___239a
IS11100___5______Database Lab____239a
IS11100___8______Program Basics___232e
IS12210___1______IT Management___211a
IS12210___2______Sys Analysis______232c
IS12210___8______Program Basics____232e
CS39006___2_____Sys Analysis______232c
CS39006___3_____Computer Design___100a
CS39006___4_____Database Tech____239a
CS39006___5_____Database Lab_____239a
CS29000___3_____Computer Design___100a
CS29000___6_____SQL______________121a
CS29000___7_____SQL Lab__________121a
CS29000___9_____Methodologies_____105a

Table 3
Classid Class-Name
1______IT Management
2______Sys Analysis
3______Computer Design
4______Database Tech
5______Database Lab
6______SQL
7______SQL Lab
8______Program Basics
9______Methodologies

RESULT SET:
StuID__Dept__Loc__IT___Sys___Comp___DB____DB__SQL_ __SQL___Prog__Methods
_________________Man_Anal___Design__Tech__Lab_____ ___Lab___Basics
IS11100_IS____Jax____N_____Y______N______Y_____Y__ ___N_____N_______Y______N
IS12210_IS____Jax____Y_____Y______N______N_____N__ ___N_____N_______Y______N
CS39006_CS___Jax____N_____Y______Y______Y_____Y___ __N_____N_______N______N
CS29000_CS___Atl____N_____N______Y______N_____N___ __Y_____Y_______N______Y

Sorry about the underscores, formatting problems lining up the columns

First you should redesign you database structure, it isn't normalized.
Should be more like this

I will work on the query with this structure.

Very true, however, the tables are actually much larger then as given, and already in place within a enterprise wide structure, so there is little but maybe some things I can do. I just wanted to pull, for simplicity sake, those fields to give as an example of my problem. But I truly appreciate any assistance you can provide.

I'm sorry, there really isn't much I can do to return the results you want, since the database structure isn't in the proper arrangement. About the only thing you could do is return the Students and the Classes they are enrolled in, but as far as doing something like a Case statement to determine 'Y' or 'N', I don't see how that is possible with the current structure.

Thanks anyway for your assistance.

You might try sending a private message to sbaxter. He's a solid database person.

I agree with Memnoch, you need to see what you can do with the table structure, but....



SQL Statement (works in MS Access)






Will have to be slightly different in SQl Server, 'First' is not valid in SQL Server, and I am not sure how Pivot statements react in SQL Server

S-

Memnoch,


I have just found your posts on joining 3 tables. I have a similar problem and I'm hoping you can help...

I have 3 tables

Table 1
EmployeeID
First Name
Last Name
Email

Table 2
ID
TaskID
EmployeeID


Table 3
TaskID
TaskName
RaisedBy (this is an employeeID)
Status (closed, new, assigned)


I would like to list all the tasks assigned to an employee, who the project manager is and who else may be assigned to the task (if any)
something like this...

TaskIdTaskName ProjectManager AssignedTo Status
123 Install Win2K DaveManager JoeDoggsBody Assigned

bob
sam

I am using ASP and Microsoft Access and I am working with the following SQL query but I just cant get it right.


sqlString = "SELECT TASK_TABLE.TASK_ID, TASK_NAME, SLAVE.FIRST_NAME, SLAVE.LAST_NAME, BOSS.FIRST_NAME, BOSS.LAST_NAME, BOSS.EMAIL, ETC, STATUS " &_
"FROM " &_

"TASK_TABLE INNER JOIN EMP_TABLE BOSS " &_
"ON TASK_TABLE.REQUEST_BY = BOSS.EMP_ID " &_

"INNER JOIN " &_

"EMP_TASK_LINK INNER JOIN EMP_TABLE SLAVE " &_
"ON EMP_TASK_LINK.EMP_ID = SLAVE.EMP_ID " &_

"ON " &_

"TASK_TABLE.TASK_ID = EMP_TASK_LINK.TASK_ID"


Thank you for any help

Rich

SELECT ETL.TaskID, TT.TaskName, ET.FirstName AS SlaveFname, ET.LastName AS SlaveLName, ET1.FirstName AS BossFname, ET1.LastName AS BossLName, ET1.Email AS BossEmail, TT.Status
FROM (EMP_TABLE AS ET1 INNER JOIN TASK_TABLE AS TT ON ET1.EmployeeID = TT.RaisedBy) INNER JOIN (EMP_TABLE AS ET INNER JOIN EMP_TASK_LINK AS ETL ON ET.EmployeeID = ETL.EmployeeID) ON TT.TaskID = ETL.EmployeeID;



s-