Home » SQL & PL/SQL » SQL & PL/SQL » Join problem, need workaround
Join problem, need workaround [message #439138] Thu, 14 January 2010 11:21 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Hi,

I'm troubleshooting an issue and in my query this is the join that is hindering the results I need.


 AND str_crwn = sbsct_crwn



This join is saying join all courses that have an assigned staff member, but I want to show all courses that have assigned staff members and no staff members..any ideas on how to add something to my code to make this happen?outer joins don't work..i need that join otherwise my query will run forever.

Re: Join problem, need workaround [message #439140 is a reply to message #439138] Thu, 14 January 2010 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
outer joins don't work..

Why?

I think the next you should post no code at all, and even just post a single line: "I have a problem in SQL, rewrite my query".

I give you a hint:

Quote:
i need that join otherwise my query will run forever

Investigate on this.

Regards
Michel

[Updated on: Thu, 14 January 2010 11:26]

Report message to a moderator

Re: Join problem, need workaround [message #439141 is a reply to message #439138] Thu, 14 January 2010 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I want to show all courses that have assigned staff members and no staff members

isn't this result set "all courses"?

Or asked a different way, which courses do not satisfy this requirement?
Re: Join problem, need workaround [message #439143 is a reply to message #439141] Thu, 14 January 2010 11:34 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Or join re-stated

staff members = courses

but i want staff members and non-assigned staff members to = courses somehows.

Re: Join problem, need workaround [message #439144 is a reply to message #439143] Thu, 14 January 2010 11:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is called an outer join.
Please tell us why you think you cannot use outer joins.
Re: Join problem, need workaround [message #439145 is a reply to message #439143] Thu, 14 January 2010 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Join problem, need workaround [message #439146 is a reply to message #439144] Thu, 14 January 2010 11:48 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
When I put the outer join:


 AND str_crwn (staff assignment table) = sbsct_crwn (course assignment table)

 AND str_crwn(+) = sbsct_crwn
 


The one course I need w/out an assigned staff doesn't show up in my output results.

Re: Join problem, need workaround [message #439147 is a reply to message #439146] Thu, 14 January 2010 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check the other conditions.

Regards
Michel
Re: Join problem, need workaround [message #439148 is a reply to message #439146] Thu, 14 January 2010 11:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you use AND and not WHERE, I assume this is not the only where-condition you used.
Why not take a walk on the wild side, and actually show us what you got? It might make things easier both for you and for us. (For example, you may have other conditions on the outer joined table without the (+) )
Re: Join problem, need workaround [message #439151 is a reply to message #439148] Thu, 14 January 2010 12:11 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I think you hit a note there...
These are the rest of the joins in my query:

WHERE T1.C1 = T2.C1
AND T3 IS NULL
AND T1.C2(+) = T4.C2  --where my join would be added
AND T4.C2 = T5.C1
AND T1.C3 = T4.C3
AND T4.C3 = T5.C4
AND T5.C4 = INPUT_VARIABLE
AND T4.C4 = T6.C1
AND T4.C5 = T6.C2
AND T4.C6 =(SELECT MAX (h.1) from table where tn.c = tb2.c2);




The rest of the joins need to be outer joined as well? I forgot that rule..is that correct?
Re: Join problem, need workaround [message #439152 is a reply to message #439138] Thu, 14 January 2010 12:18 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes, when outer joining a table every reference to that table in the where clause must contain the outer join operator (+)
Re: Join problem, need workaround [message #439153 is a reply to message #439151] Thu, 14 January 2010 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The rest of the joins need to be outer joined as well

Yes otherwise the other condition will not return TRUE as if the outer join is not met all the row columns are NULL and NULL is equal, not equal, less than or greater than nothing.

Regards
Michel

[Updated on: Thu, 14 January 2010 12:23]

Report message to a moderator

Re: Join problem, need workaround [message #439154 is a reply to message #439152] Thu, 14 January 2010 12:25 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
When you say:

..when outer joining a table every reference to that table in the where clause must contain the outer join operator (+) 



so which table T1 and T4 or just T1? because I can see getting an error message of:


ora-01417 error message easily - a table may be outer joined to at most one other table. 
Re: Join problem, need workaround [message #439155 is a reply to message #439138] Thu, 14 January 2010 12:26 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you need to use the ansi syntax to get around that.
Re: Join problem, need workaround [message #439156 is a reply to message #439155] Thu, 14 January 2010 12:28 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I was afraid you would say that....
Re: Join problem, need workaround [message #439159 is a reply to message #439155] Thu, 14 January 2010 12:45 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Can you provide a little help on the ansi join just to get me started..i'm still a novice at the ansi joins..

Thanks
Re: Join problem, need workaround [message #439160 is a reply to message #439159] Thu, 14 January 2010 12:54 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

Examples:
http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/Catalog0140__Table-Joins.htm
http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/AnExampleofaRightOuterJoin2.htm
http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/AnExampleofaLeftOuterJoin2.htm
Re: Join problem, need workaround [message #439173 is a reply to message #439153] Thu, 14 January 2010 14:50 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I'm waiting for my question to be answered..When you say:


...when outer joining a table every reference to that table in the where clause must contain the outer join operator (+) 





so which table T1 and T4 or just T1?
Re: Join problem, need workaround [message #439174 is a reply to message #439173] Thu, 14 January 2010 14:56 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Oracle recommends using ANSI style OUTER JOIN than using operator for outer joins. That'll help you to overcome outerjoin limitations and also readability is better.
ORA-01417: a table may be outer joined to at most one other table 
Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
 
Action: Check that this is really what you want, then join b and c first in a view.
 


SQL JOINS:

http://www.psoug.org/reference/joins.html
Quote:

It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

[Updated on: Thu, 14 January 2010 15:06]

Report message to a moderator

Re: Join problem, need workaround [message #439176 is a reply to message #439173] Thu, 14 January 2010 15:06 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm waiting for my question to be answered..
As opposed to simply running the SQL yourself?
Why wait?
Previous Topic: Multi-Language Charachters & XML Error!
Next Topic: Compound bitmap join index on one dimension (merged 2)
Goto Forum:
  


Current Time: Sat Dec 10 20:36:37 CST 2016

Total time taken to generate the page: 0.05916 seconds