CODEWITHSUNDEEP
sql
A B
A B

Reputation: 131

SQL query that limits results based on appearance in other tables

I'm a SQL novice, but need to write some SQL statements for a Java program that has to interact with a database. Our Java textbook covers only very basic SQL commands, and I am having trouble getting a more advanced (by my standards) one to work.

Here's the situation:

The database has 5 tables.

Teacher: TeacherID (PK), LastName
Class: ClassID (PK), Description
Room: Building, Room Number, PK is the combo of those two
TeachingAssignments: TeacherID(FK), ClassID(FK)
ClassRoomAssignments: ClassID(FK), Building, Room Number(combo is FK)

I need to give just the LastName, ClassID, and Building of only those teachers, classes, and rooms that are fully assigned. I.e., if a class both has a teacher and a room assignment, then I need to give that class's ID, the assigned teacher's last name, and the assigned building.

I have little idea how to proceed.

I've been playing around with statements like the following but they aren't working for me: 

SELECT Teacher.LastName, Class.ClassID, Room.Building 
FROM Teacher, Class, Room, TeachingAssignments, ClassRoomAssignments 
WHERE Teacher.TeacherID = TeachingAssignments.TeacherID 
    AND Room.Building = ClassRoomAssignments.Building
    AND Class.ClassID = TeachingAssignments.ClassID
    AND Class.ClassID = ClassRoomAssignments.ClassID

Can anyone help? Thanks!

Upvotes: 0

Views: 96

Answers (1)

Luis Vargas
Luis Vargas

Reputation: 76

Your problem is that you need to add the respective joins for your table.

instead of doing:

SELECT Teacher.LastName, Class.ClassID, Room.Building 
FROM Teacher, Class, Room, TeachingAssignments, ClassRoomAssignments 
WHERE Teacher.TeacherID = TeachingAssignments.TeacherID 
AND Room.Building = ClassRoomAssignments.Building 
AND Class.ClassID = TeachingAssignments.ClassID 
AND Class.ClassID = ClassRoomAssignments.ClassID

you need something like that

SELECT Teacher.LastName, Class.ClassID, Room.Building 
FROM 
Teacher INNER JOIN TeachingAssignments
  ON Teacher.TeacherID = TeachingAssignments.TeacherID 
INNER JOIN Class
  ON Class.ClassID = TeachingAssignments.ClassID
INNER JOIN ClassRoomAssignments
  ON Class.ClassID = ClassRoomAssignments.ClassID
INNER JOIN Room
  ON Room.Building = ClassRoomAssignments.Building

As you can see every INNER Join is followed by it respective ON clause which is in charge of designing which element is going to be joined.

Upvotes: 1

Related Questions