Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

JOIN query between 3 tables

Attached is an image of my DB and relations between the tables.  I would like to query all the records in tblOrgHours where the  MONTH(tblOrgHours.ActivityDate) = 5.  I would like to also have the ActivityName of each associated ActivityID.  Also the [First Name] and [Last Name] of the each associated RegID  

How do I formulate an sql statement to get all these values in one query?
dbs-structure.jpg
SOLUTION
Avatar of Jason Schlueter
Jason Schlueter
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of al4629740

ASKER

with acitivityname it would be

SELECT tor.[First Name],
       tor.[Last Name],
       tor.birthdate,
       tor.regid,
       tor.agencyid,
       toh.activityid,
         toa.[activity name]
FROM   tblorgregistrations tor
       JOIN tblorghours toh
         ON tor.regid = toh.regid
       JOIN tblorgactivities toa
         ON toa.activityid = toh.activityid
WHERE  Month(toh.activitydate) = 5
What the difference between what you gave me and this:

select max(r.[Last Name])LastName, max(r.[First Name])FirstName, max(r.BirthDate)Birthdate,max(H.Hours)Hours, max(ActivityDate)ActivityDate 
from tblOrgRegistrations R
left Join tblOrgHours H on R.RegID = H.RegID
left Join tblOrgActivities A on A.ActivityID = H.ActivityID where  Month(H.activitydate) = 5 group by ActivityDate, [Last Name],[First Name],BirthDate,h.Hours

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial