SQL statement to connect 5 tables

Question

Ok, so i want to populate a gridview with a rather complex statement that i have no idea how to get, I dont even know if its possible. five tables and a view are involved, these arent all the fields in the tables but just to make things clearer, i only put the required fields.

Student Table:-

Student_ID
Specialization_ID

Specialization Table:-

ID
Specialization_Name
Department_ID

Staff Table:-

ID
Staff_ID 
Department_ID

Department Table:-

ID
Department_Name

Survey Table:- (Username is the uploader)

ID
Username

Users View:- (Here the Username, Password is taken from both the student table and the staff table)

Username
Password
UserType

This is how i created the view:-

CREATE View [Users] as
Select Student_ID as Username, Password,'STU' as UserType from Student
union
Select Staff_ID, Password, 'STF' as UserType from Staff

Now, considering that when a staff member signs in, his username is stored in session Session["Username"], what i want to pull from the database, are all the surveys that are uploaded by users (only students) that have a specialization that belongs to the current user's department. considering that all relationships (foreign keys) are set, what is the sql query i should use? or the method at least.

I can't find the relation between the first 4 tables and the last two. How do you relate a UserName to the students? — Steve, Dec 13 '12 at 23:44
What does "his username is stored in session `Session["Username"]` mean? What is `Session["Username"]`? — RBarryYoung, Dec 13 '12 at 23:48
@RBarryYoung Iam storing the current logged in user's login ID in session, using the session function is asp.net c#. — BasharKH, Dec 14 '12 at 00:04
@Steve I edited my question to show you how i created the view. — BasharKH, Dec 14 '12 at 00:07

score 1 · Answer 1 · answered Dec 13 '12 at 23:50

In short: First try to figure out the connections between your tables (FK and PK - for a start point, it looks like Specialization, Staff and Department tables do have relation).

Then come up with the query that works. Try to convert that to a view and depending on number of records measure the performance.

Although it is a guideline how to proceed, i am pretty sure that will help.

score 0 · Answer 2 · answered Dec 14 '12 at 01:31

0

Ok, so i solved it using subqueries after a an hour of grinding ...Thanks all for the help.

    Select * From Survey where Username IN 
(Select Student_ID from Student where Specialization_ID IN 
(Select ID from Specialization where Department_ID = 
(Select Department_ID from Staff where Staff_ID =@Username)))

answered Dec 14 '12 at 01:31

BasharKH

117
1
3
14

You should look at `JOIN` instead of `subquery` (as suggested by "ElYusubov". `subquery` is probably the worst option you could take here. – Germann Arlington Dec 17 '12 at 18:11

score 0 · Answer 3 · edited May 23 '17 at 11:52

May I ask why there isn't another table between the user and the staff?

It could possibe be called UserStaffTable which would have the two id columns as a composite key, and two extra columns referencing the specialization and departmenttable? This way you always have a list of students that belong to a staff member, and you could filter them out based on specializations, and/or departments.

This will allow you to perform a join rather than a subquery.

According to this SO answer subqueries are slower, if this at all matters.

SQL statement to connect 5 tables

3 Answers3