Im very new to subqueries.
I have 2 queries that I need to combine.
I have a database with User Tables, Activity Tables, Activity Registration Tables, and Activity Attempt Tables
I need to find the Activity REgistrations for a given user and the Attempt info for each registration.
I have two separate queries that find the info, but I need to combine them so I can return all the values in a single row (which makes actions on the row easier)
Right now I have a dtalist that returns the list of registrations with a button in a datalist. Clicking the button calls the 2nd query to find the Attempt data for the selected activity. But that process requires too much user interaction. My final result needs to be a hyperlink in the datalist that contains the attempt data for the activity.
//This query returns the user's list of Activities
SELECT TOP (5) TBL_TMX_Activity.ActivityName, TBL_TMX_Activity.Activity_PK
FROM TBL_TMX_Registration INNER JOIN
TBL_TMX_Activity ON TBL_TMX_Registration.ActivityFK = TBL_TMX_Activity.Activity_PK INNER JOIN
tblEmp INNER JOIN
iwc_Usr ON tblEmp.Emp_PK = iwc_Usr.Usr_EmpFK ON TBL_TMX_Registration.EmpFK = tblEmp.Emp_PK
WHERE (iwc_Usr.Usr_Name = N'matt.fox') AND (TBL_TMX_Registration.Status <> 4) AND (TBL_TMX_Activity.CBTLaunchMtdFK IS NOT NULL)
ORDER BY TBL_TMX_Registration.LstUpd DESC, TBL_TMX_Activity.Activity_PK DESC
//This query returns the Attempt data for the user's activity
SELECT TBL_TMX_Attempt.Attempt_PK, TBL_TMX_ActCBT.PackageId
FROM iwc_Usr INNER JOIN
tblEmp ON iwc_Usr.Usr_EmpFK = tblEmp.Emp_PK INNER JOIN
TBL_TMX_Activity INNER JOIN
TBL_TMX_Attempt ON TBL_TMX_Activity.Activity_PK = TBL_TMX_Attempt.ActivityFK INNER JOIN
TBL_TMX_ActCBT ON TBL_TMX_Attempt.ActivityFK = TBL_TMX_ActCBT.ActivityFK ON tblEmp.Emp_PK = TBL_TMX_Attempt.EmpFK
WHERE (iwc_Usr.Usr_Name = N'matt.fox') AND (TBL_TMX_Activity.Activity_PK = 6609)
//Can these two queries be combined into 1 that returns a list of activities and the attempt data for each activity?