0

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

Edited by happygeek: moved

4
Contributors
3
Replies
31
Views
4 Months
Discussion Span
Last Post by xrj
0

Prefixing tables with tbl is pointless and makes reading the SQL much more difficult.

Line 2 looks wrong, you can't comma separate join criteria, you should use and instead (and maybe add brackets for clarity).

Now, describe in one sentence the data which you want to retrieve.

1

jointables.png

SELECT e.classid, 
       a.planname, 
       b.workoutname,
       e.date,
       c.timeslot,
       e.status,
       d.staffemail
FROM   tblclass e
JOIN   tblallocation f ON e.planallocationid = f.planallocationid
JOIN   tblplan a     ON a.planid = f.planid
JOIN   tblworkout b  ON b.workoutid = f.workoutid
JOIN   tblstaff d  ON d.staffid = e.staffid
JOIN   tbltimeslot c ON c.timeslotid = e.timeslotid
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.