Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join with Wildcard
Outer Join with Wildcard [message #293732] Mon, 14 January 2008 15:09 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Simple question... can I outer join with a wild card?

For example:

Table 1:
Name State
Bob   ID
Jack  UT
Sally AZ


Table 2:
Name          User
Bob Anderson  07458
Jack Spratt   06132
Sally Logan   07717



I want to join Table 1 with Table 2 based on Name.

For example
SELECT A.Name, B.User 
FROM Table1 A SELECT NAME,USER FROM Table2 B 
WHERE B.Table2 (+)= A.Table1


Would a join statement similar to
WHERE B.Table2 LIKE A.Table1 
Work?

[Updated on: Mon, 14 January 2008 15:13] by Moderator

Report message to a moderator

Re: Outer Join with Wildcard [message #293733 is a reply to message #293732] Mon, 14 January 2008 15:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to join Table 1 with Table 2 based on Name.
For example
SELECT A.Name, B.User 
FROM Table1 A SELECT NAME,USER FROM Table2 B 
WHERE B.Table2 (+)= A.Table1


This query is completly meaningless and can't even start to compile.

But yes you can outer join with any operator.

Regards
Michel

Re: Outer Join with Wildcard [message #293734 is a reply to message #293733] Mon, 14 January 2008 15:23 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
I typically use the (+)= instead of the phrase "OUTER JOIN".

You are saying if I put OUTER JOIN ON TABLE1.ID LIKE TABLE2.ID

it would join a name such as "JOHN DOE" to JOHN ?
Re: Outer Join with Wildcard [message #293735 is a reply to message #293732] Mon, 14 January 2008 15:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, you can join the tables with condition like
WHERE b.name LIKE a.name||' %'
, but note that in case you have eg. 3 rows with NAME = 'Bob' in Table1 and 5 rows with NAME LIKE 'Bob %' in Table2, your result will contain all their combinations, that means 15 rows.
There is nothing to do with this "feature", except complete change of the design.

By the way, you do NOT want to achieve outer join in your example, see here or check the documentation what OUTER JOIN is.

Of course, you may change it to outer join by adding the (+) operator to any column identifier, eg. B.NAME(+). But that just assures the row will be displayed even without matching row in Table1.

[Edit: Added last paragraph]

[Updated on: Mon, 14 January 2008 15:38]

Report message to a moderator

Previous Topic: get null record with 0 value
Next Topic: Best way to refresh data
Goto Forum:
  


Current Time: Thu Dec 08 18:35:52 CST 2016

Total time taken to generate the page: 0.10230 seconds