Home » SQL & PL/SQL » SQL & PL/SQL » Multiple joins through multiple tables (Oracle 8)
|Multiple joins through multiple tables [message #352830]
||Thu, 09 October 2008 16:50
Registered: October 2008
Location: Pittsburgh, PA
Hello, I am new to the boards. I need assistance with a joining issue in my database.|
I have the following schema.
project 1-to-1 Totals_from_amount
project 1-to-1 person
person 1-to-1 organization
organization 1-to-1 address
I am trying to get all records from the database by doing a right outer join with the project to organization table and further from the organization to address table. Otherwise, some projects may be skipped.
Hence in psuedocode, I am trying the following.
get project fields
right outer join to person on person id
right outer join to organization on organization id
The following code is producting a lot of repeat records. What am I doing wrong?
R.corId, R.csId, R.coId,
T.totalAllotment, T.totalAtIssue, T.totalInitiations, T.totalObligations, T.totalCosts,
E1.lastName as lastName1, E2.lastName as lastName2, E3.lastName as lastName3, E4.lastName as lastName4,
O1.orgName as orgName1, O2.orgName as orgName2, O3.orgName as orgName3, O4.orgName as orgName4
Person E1, Person E2, Person E3, Person E4,
Organization O1, Organization O2, Organization O3, Organization O4
(R.projectId = T.projectId)
AND (T.totalInitiations > 200000000)
AND (R.corId = E1.personId(+) AND E1.orgId = O1.orgId(+))
AND (R.csId = E2.personId(+) AND E2.orgId = O2.orgId(+))
AND (R.coId = E3.personId(+) AND E3.orgId = O3.orgId(+))
AND (R.doeOrganizationId = O4.orgId(+))
ORDER BY T.totalAllotment;
Please note the corId, csId and coId are person IDs.
[Updated on: Thu, 09 October 2008 16:53]
Report message to a moderator
|Re: Multiple joins through multiple tables [message #352833 is a reply to message #352830]
||Thu, 09 October 2008 17:45
Registered: December 1999
Location: Connecticut USA
The first thing wrong was you forgot to format your code. There is a code formatting tool on the home page (that would be the old home page, maybe not the new WIKI page). Formatting your code produces this:|
e1.LastName AS LastName1,
e2.LastName AS LastName2,
e3.LastName AS LastName3,
e4.LastName AS LastName4,
o1.OrgName AS OrgName1,
o2.OrgName AS OrgName2,
o3.OrgName AS OrgName3,
o4.OrgName AS OrgName4
FROM Project r,
WHERE (r.ProjectId = t.ProjectId)
AND (t.TotalInitiations > 200000000)
AND (r.cOrId = e1.PersonId (+)
AND e1.OrGid = o1.OrGid (+) )
AND (r.csId = e2.PersonId (+)
AND e2.OrGid = o2.OrGid (+) )
AND (r.coId = e3.PersonId (+)
AND e3.OrGid = o3.OrGid (+) )
AND (r.DoeOrganizationId = o4.OrGid (+) )
ORDER BY t.TotalAllotment;
Normally, when a query is returning duplicate rows, it is because a join in missing in the query somewhere.
So it is with this query. The error is at the bottom of the where clause. There is no join to Person e4.
Do not feel bad. This is an easy mistake to make, especially if you are not used to doing many table joins. Good news is you will get better at it over time.
Good luck, Kevin
Current Time: Wed Jan 18 23:07:20 CST 2017
Total time taken to generate the page: 0.15418 seconds