Member Avatar for 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
Edited by happygeek because: moved
  • 4 Contributors
  • 3 Replies
  • 453 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by xrjf

Dani AI

Generated

A few practical points to make the task simpler and avoid the common mistakes already showing up in the thread.

’s approach (start from the class table, join allocation, then plan/workout, and add staff/timeslot) is the right pattern. The original post has two immediate problems to fix first: a comma used where an ON/AND belongs, and a small typo in the staff table name. Also double-check the actual FK/PK column names in your schema — for example the allocation key might be named planallocation in one table and planallocationid in the other; that mismatch will break the join.

Work through the joins incrementally while testing in SSMS: run the class table alone, add the allocation join and confirm rows, then add plan/workout, etc. If some classes can lack a staff assignment or timeslot, use LEFT JOIN for those tables so you don’t lose rows. Limit selected columns (avoid SELECT *) and add a WHERE clause early (for example a date range) to keep result sets small while developing.

Performance and safety: ensure your FK columns are indexed so the multi-way join stays fast; avoid building SQL by string concatenation in ASP.NET — use parameterized commands; and consider bracketing ambiguous names (for example [date] or [status]) or renaming them to avoid confusion with SQL types/keywords.

Binding hint (no SQL shown — use your corrected JOIN): 

using(var conn = new SqlConnection(connString))
using(var cmd = new SqlCommand(yourSql, conn))
using(var da = new SqlDataAdapter(cmd))
{
    var dt = new DataTable();
    da.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

Quick checklist: confirm exact column names, fix typos, build joins one at a time, decide INNER vs LEFT JOIN based on optional data, add filters and indexes, and use parameters for any user-supplied values.

Recommended Answers

All 3 Replies

Member Avatar for Member #120589

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

Member Avatar for 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.

Member Avatar for xrjf

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.