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 Go to next message
VanJay011379
Messages: 3
Registered: October 2008
Location: Pittsburgh, PA
Junior Member
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?

SELECT 
R.projectId, 
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 
FROM 
project R, 
Totals_from_amount T, 
Person E1, Person E2, Person E3, Person E4, 
Organization O1, Organization O2, Organization O3, Organization O4 
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;


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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
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:

SELECT   r.ProjectId,
         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
FROM     Project r,
         Totals_From_Amount t,
         Person e1,
         Person e2,
         Person e3,
         Person e4,
         Organization o1,
         Organization o2,
         Organization o3,
         Organization o4
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
Re: Multiple joins through multiple tables [message #352838 is a reply to message #352830] Thu, 09 October 2008 20:10 Go to previous messageGo to next message
VanJay011379
Messages: 3
Registered: October 2008
Location: Pittsburgh, PA
Junior Member
Fixed! Thanks! Ugh. Staring at the statement too long and I overlook something so simple.

Why does the lack of a join cause repeated results anyway?

[Updated on: Thu, 09 October 2008 20:17]

Report message to a moderator

Re: Multiple joins through multiple tables [message #352848 is a reply to message #352830] Thu, 09 October 2008 22:28 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Because Lack of PROPER join will result in Cartesian Product .

Thumbs Up
Rajuvan.

[Updated on: Thu, 09 October 2008 22:28]

Report message to a moderator

Previous Topic: Update query - ora-01427 error
Next Topic: primary key issue
Goto Forum:
  


Current Time: Mon Dec 05 21:11:50 CST 2016

Total time taken to generate the page: 0.11900 seconds