JOIN, how many?

Hi, I'm wondering how many JOIN you can put in one statement. I got 2 working, but when I try to add a third one I get an error. If I need a third JOIN to retreive a name from another table for ex., how do I go about it?

thanks
giana

Select blah1 from Tbl1 inner join (Tbl2 inner join Tbl3 on Tbl2.blah2 = Tbl3.blah3) on Tbl1.blah1 = Tbl2.blah2

Where Blah represents the relational fields,

Good luck

Select blah1 from Tbl1 inner join (Tbl2 inner join Tbl3 on Tbl2.blah2 = Tbl3.blah3) on Tbl1.blah1 = Tbl2.blah2

Where Blah represents the relational fields,

Good luck

ok, maybe I wasn't clear enough. That is what I have (2 INNER JOINs). But I need one more: if I try to nest it in the second JOIN I get an error.

SELECT Blah1 FROM (Tbl1 INNER JOIN (Tbl2 INNER JOIN Tbl3 ON Tbl2.Blah2 = Tbl3.Blah3) ON Tbl1.Blah1 = Tbl2.Blah2) INNER JOIN Tbl4 ON Tbl1.Blah1 = Tbl4.Blah4

Phew!

post your select statement as far as i know access can execute upto 4 or 5 recursive select.

Here it is:

SELECT D.EventID, EV.EventName, DP.DepartmentName AS Department, D.DepartmentID, E.Name AS Employee, D.EmployeeID, D.Date, D.RegularHours AS [Regular Hours], D.OvertimeHours AS [Overtime Hours], D.TotalRegular AS [Regular Billing], D.TotalOvertime AS [Overtime Billing]" & _
FROM Employees E INNER JOIN(Departments DP INNER JOIN(EventsEV INNER JOIN Details D ON EV.EventID=D.EventID)ON DP.DepartmentID=D.DepartmentID)ON E.EmployeeID=D.EmployeeID"

Here it is:

SELECT D.EventID, EV.EventName, DP.DepartmentName AS Department, D.DepartmentID, E.Name AS Employee, D.EmployeeID, D.Date, D.RegularHours AS [Regular Hours], D.OvertimeHours AS [Overtime Hours], D.TotalRegular AS [Regular Billing], D.TotalOvertime AS [Overtime Billing]" & _
FROM Employees E INNER JOIN (Departments DP INNER JOIN(EventsEV INNER JOIN Details D ON EV.EventID=D.EventID) ON DP.DepartmentID=D.DepartmentID) ON E.EmployeeID=D.EmployeeID"

where's EV comes from?

where's EV comes from?

the parenthesis after the second INNER JOIN

FROM Employees E INNER JOIN (Departments DP INNER JOIN(Events EV INNER JOIN Details D ON EV.EventID=D.EventID) ON DP.DepartmentID=D.DepartmentID) ON E.EmployeeID=D.EmployeeID


Ok, maybe this will make everything a little clearer:
The table Details is the one with EventID, EmployeeID and DepartmentID.
The other tables where the actual names are, are the Department table, the Employee table and the Event table.

actually i have a query i made it using the insert query of access and it's almost the same as your query except that this one is working


SELECT Result.[Name], Courses.Course, Major.Major, Major.Description, TuitionFee.Tuition, TuitionFee.Tuition, TuitionFee.Lab, Result.Controlno
FROM Courses LEFT JOIN (Major LEFT JOIN (TuitionFee LEFT JOIN Result ON Result.Controlno = TuitionFee.Controlno) ON TuitionFee.Major=Major.Major) ON Courses.Course=Major.Course
WHERE Courses.Course=pCourse;


did you try your select in a few column? try it and see what happend

still doesn't work. I can turn around it I think, but I need one more input. In a select statement like this:


"SELECT E.EventID, E.EventName AS Event, Sum(D.Hours) AS Hours, Sum(D.TotalBilling) AS Billing" & _
"FROM Events E LEFT JOIN Details D ON E.EventID = D.EventID" & _
" WHERE (((E.EventID)=[D].[EventID])) GROUP BY E.EventName"

If I do not include D.EventID in the statement everything works, but if I do it doesn't. Anybody knows how to make it work including the EventID?

SELECT Events.EventID, Events.EventName, Sum(Details.Hours) AS Hours, Sum(Details.TotalBilling) AS Billing
FROM Events INNER JOIN Details ON Events.EventID = Details.EventID
GROUP BY Events.EventID, Events.EventName;


--hulett

or

SELECT Events.EventID, Max(Events.EventName), Sum(Details.Hours) AS Hours, Sum(Details.TotalBilling) AS Billing
FROM Events INNER JOIN Details ON Events.EventID = Details.EventID
GROUP BY Events.EventID;

thanks to the both of you guys
it finally works!!!!!