Link to home
Start Free TrialLog in
Avatar of thomas455
thomas455

asked on

SQL query joining 6 tables in asp.net

Hello experts,

I have to write a sql joining 6 tables. to retrieve classid, planname, workoutname,date,timeslot,status,staffemail and display them to a gridview.

the tables are as below

tblclass having columns classid(PK), planallocationid(FK to tblallocation), date, timeslotid(FK to tbltimeslot),status, staffid(FK to tblstaff)

tblallocation having columns planallocation(PK), planid(FK to tblplan), workoutid(FK to tblworkout)

tblplan having columns planid(PK), planname

tblworkout having columns workoutid(PK), workoutname

tblstaff having columns staffid(PK), staffemail

tbltimeslot having columns timeslotid(PK), timeslot

i came up with something like below but am getting lost with all these. Can anyone help!
SELECT a.planname, b.workoutname, c.timeslot, e.date, e.status, d.email from tblclass e
inner join tblallocation f on f.planid=a.planid, f.workoutid=b.workoutid
inner join tbltimeslot c on c.timeslotid=e.timeslotid
inner join tclstaff d on d.staffid=e.staffid 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thomas455
thomas455

ASKER

Yes works perfectly. thank you for your help
Thomas, you only had a single issue and that was you missed the JOIN with the tblworkout:
SELECT a.planname, b.workoutname, c.timeslot, e.date, e.status, d.email from tblclass e
inner join tblallocation f on f.planid=a.planid, f.workoutid=b.workoutid
inner join tbltimeslot c on c.timeslotid=e.timeslotid
inner join tclstaff d on d.staffid=e.staffid


Ryan's solution should work perfectly to you, but if you to have a better understanding how the tables are joined you might want to go for something like:
SELECT p.planname. w.workoutname, c.date, t.timeslot, c.status, s.staffemail
FROM tblClass c
	INNER JOIN tblAllocation a 
		INNER JOIN tblPlan p ON a.planid = p.planid
		INNER JOIN tblWorkout w ON a.workoutid = w.workoutid
		ON c.planallocationid = a.planallocation
	INNER JOIN tbltimeslot t ON c.timeslotid = t.timeslotid
	INNER JOIN tblStaff s ON c.staffid = s.staffid

Open in new window

NOTE: There's no difference between these two solutions besides how you read it.
Just out of wild curiosity, why are you parsing this T-SQL in the front-end asp.net as opposed to the back end SQL Stored Procedure?   Having it in an SP means it's pre-compiled with an execution plan stored so it will run faster, and easier to maintain.