SQL query joining 6 tables in asp.net

[Alv45525]

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

[diafol]

You'll get more joy posting to the relevant forum.

[pty]

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.

[xrjf]

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