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,sta ffemail 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!
I have to write a sql joining 6 tables. to retrieve classid, planname, workoutname,date,timeslot,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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
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.
ASKER