JOIN, how many?

giana
01-15-2004, 08:34 AM
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

Rossc007
01-15-2004, 09:00 AM
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

giana
01-15-2004, 09:19 AM
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.

Rossc007
01-15-2004, 09:27 AM
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!

Dennis DVR
01-15-2004, 09:34 AM
post your select statement as far as i know access can execute upto 4 or 5 recursive select.

giana
01-15-2004, 09:43 AM
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"

Dennis DVR
01-15-2004, 09:47 AM
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?

giana
01-15-2004, 09:53 AM
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.

Dennis DVR
01-15-2004, 10:41 AM
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

giana
01-15-2004, 11:09 AM
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?

hulett819
01-15-2004, 11:15 AM
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

Dennis DVR
01-15-2004, 11:17 AM
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;

giana
01-15-2004, 11:30 AM
thanks to the both of you guys
it finally works!!!!!

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum