Databases
StackOverflow2013
Postsdbo
POJoin all items in 4 tables allowing nulls
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoin all items in 4 tables allowing nulls
    primarykey
    data
    text
    <p>I'm working on a training program and I'm at a point where I can’t figure out the SQL query. I have 4 tables: userProfilesTbl, trainingTbl, userAssessmentTbl and setsTbl userProfilesTbl and userAssessmentsTbl are related through the UserId fkey which is a unique identity field type.</p> <p>trainingTbl and userAssessmentTbl are related through the tt_id fkey INT field type. setsTbl and trainingTbl are related through the s_id fkey INT field type.</p> <p>The trainingTbl is populated with training carried out on specified dates by specified user groups.</p> <p>The userAssessmentTbl is populated by the individual users training (outcomes, notes etc).</p> <p>The userProfilesTbl holds all additional user information not included in the membership and roles framework (connects to aspnet_users table).</p> <p>The setsTbl is populated by a list of the different types of training.</p> <p>I want to show all training sets and users regardless if they’ve done the training or not.</p> <p>The Gridview (which is a PIVOT table) should be returning surName, from the userProfilesTbl, setName from the setsTbl and outcome (value) from the userAssessmentTbl.<br> At present I have 2 select statements </p> <ol> <li><p>shows the setName and surname without any training outcomes</p> <p><code>Dim query As String = "SELECT * FROM userProfilesTbl LEFT OUTER JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT OUTER JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT OUTER JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC</code></p></li> <li><p>shows the training outcome setName, surname only of the users who have done the training and only the training they have carried out. All other users and training are not displayed.</p> <p><code>SELECT * FROM userProfilesTbl, setsTbl, userAssessmentTbl, trainingTbl WHERE (userProfilesTbl.UserId = userAssessmentTbl.UserId) AND (userAssessmentTbl.tt_id = trainingTbl.tt_id) AND userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC</code></p></li> </ol> <p>I’m stumped any suggestions?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDev N00B
    1. UStheBo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJoin all items in 4 tables allowing nulls
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload