Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian Join
Cartesian Join [message #273026] |
Mon, 08 October 2007 14:16  |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
Hi all be easy on me I am a little confused on my joins.
It seems like I have all the joins correct, but the query is running for ever in Toad, so I assumed I'm having a cartesian issue. In order to query data from more than one table, do I need to identify common columns within each table that relates to all the tables?
See query below:
SELECT pa.segment1, gra.task_id, a.award_id
FROM apps.gms_resource_assignments gra,
apps.gms_budget_lines bl,
apps.pa_resource_list_members rlm,
apps.pa_resources r,
apps.pa_projects_all pa,
apps.gms_awards_all a
WHERE rlm.resource_id = r.resource_id
AND gra.resource_list_member_id = rlm.resource_list_member_id
AND bl.resource_assignment_id = gra.resource_assignment_id
AND pa.project_id = gra.project_id(+)
AND pa.project_id = a.award_project_id(+)
|
|
|
Re: Cartesian Join [message #273027 is a reply to message #273026] |
Mon, 08 October 2007 14:28   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>SELECT pa.segment1, gra.task_id, a.award_id
since you are getting data from 3 table, then only those 3 table should be in the FROM clause.
Subordinate the remaining tables into the WHERE clause & use either EXISTS or IN with these 3 tables.
> AND pa.project_id = gra.project_id(+)
> AND pa.project_id = a.award_project_id(+)
On the surface both of these just don't seem to belong here.
What benefit does either OUTER JOIN add to this SELECT?
[Updated on: Mon, 08 October 2007 14:38] by Moderator Report message to a moderator
|
|
|
Re: Cartesian Join [message #273028 is a reply to message #273027] |
Mon, 08 October 2007 15:15   |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
Sorry I forgot to add the other columns to the query. Here is the new query.
SELECT pa.segment1, gra.task_id, a.award_id, gra.BUDGET_VERSION_ID, bl.BURDENED_COST budget_total,
decode(substr(r.name,1,10) ,'Award Reve',nvl(bl.BURDENED_COST,0),
'F & A Cost',nvl(bl.BURDENED_COST,0),0)indirect_budget,
decode(substr(r.name,1,10) ,'Award Reve',0,
'F & A Cost',0,
'Cost Share',0,
nvl(bl.BURDENED_COST,0)) direct_budget,
decode(substr(r.name,1,10),
'Cost Share',nvl(bl.BURDENED_COST,0),0)cs_budget
FROM apps.gms_resource_assignments gra,
apps.gms_budget_lines bl,
apps.pa_resource_list_members rlm,
apps.pa_resources r,
apps.pa_projects_all pa,
apps.gms_awards_all a
WHERE rlm.resource_id = r.resource_id
AND gra.resource_list_member_id = rlm.resource_list_member_id
AND bl.resource_assignment_id = gra.resource_assignment_id
AND pa.project_id = gra.project_id(+)
AND pa.project_id = a.award_project_id(+)
|
|
|
Re: Cartesian Join [message #273029 is a reply to message #273027] |
Mon, 08 October 2007 15:23   |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
The main problem now is that no award_id appears in the column once I run the query. There should be some award_ids, so I'm wondering if my joins are not setup properly. Any advice?
|
|
|
Re: Cartesian Join [message #273042 is a reply to message #273026] |
Mon, 08 October 2007 21:51  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I am afraid that without knowing your data you will get only guesses.
But if you would rewrite the WHERE condition into the order tables are joined:
WHERE pa.project_id = a.award_project_id(+)
AND pa.project_id = gra.project_id(+)
AND gra.resource_assignment_id = bl.resource_assignment_id
AND gra.resource_list_member_id = rlm.resource_list_member_id
AND rlm.resource_id = r.resource_id
you can figure out, that:
PA is OUTER JOINED with A
PA is OUTER JOINED with GRA; but as GRA is INNER JOINED with other tables, this reduces the PA-GRA JOIN to INNER too.
Try to OUTER JOIN GRA with other tables (adding (+) operator into the right sides of this WHERE condition).
As for query performance, first read Performance Tuning tab in the OraFAQ Forum Guide; if you get lost, post here appropriate information (at least EXPLAIN PLAN, number of rows in the tables/resultset, primary keys/indexes).
|
|
|
Goto Forum:
Current Time: Fri Feb 07 18:39:00 CST 2025
|