Home » SQL & PL/SQL » SQL & PL/SQL » Join Statement (Oracle 9.2)
Join Statement [message #359794] Tue, 18 November 2008 04:07 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi all.

My 2 tables are as follow.

Table ITEM
Item | Supp |
-------------
1 | 22
2 |
3 | 11

Table SUPPLIER
Supp | Name
--------------
11 | ABC
22 | DEF
33 | ZYX

My Script.

Select A.item b.name from ITEM A
join SUPPLIER B
on A.SUPP = B.SUPP

My results.

Item | NAME |
-------------
1 | DEF
3 | ABC

I want it not to loose my second item. It can even display it then as..


Item | NAME |
-------------
1 | DEF
2 |
3 | ABC

or


Item | NAME |
-------------
1 | DEF
2 | NONE
3 | ABC

Please Help.
Re: Join Statement [message #359797 is a reply to message #359794] Tue, 18 November 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 14 October 2008 11:09
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


And I add:
Post a test case: create table and insert statements along with the result you want with these data.

Search in the doc for OUTER JOIN.

Regards
Michel
Re: Join Statement [message #359949 is a reply to message #359797] Tue, 18 November 2008 23:14 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
Try reading up on outer joins, left joins or right joins.
Re: Join Statement [message #359958 is a reply to message #359794] Tue, 18 November 2008 23:50 Go to previous messageGo to next message
navneet_iway
Messages: 2
Registered: November 2008
Junior Member
if u want to take the all value from left hand side of table u must use the LEFT OUTER JOIN

TO GET your OWN RESULT SET U MUST USE THE FOLLOWING SQL QUERIES

Select A.item ,b.name from ITEM A left outer
join SUPPLIER B
on A.SUPP = B.SUPP order by item


Re: Join Statement [message #359962 is a reply to message #359794] Wed, 19 November 2008 00:14 Go to previous messageGo to next message
vijaykrishna2005
Messages: 1
Registered: November 2008
Junior Member
Hi you are using simple join condition which can't work for your requirement.

SQL>Select A.item,B.Name from......where A.supp (+)= B.supp;

This is known as left outer join which will return all the rows in left hand side table even there are no matching records in the right side table.
Re: Join Statement [message #359969 is a reply to message #359962] Wed, 19 November 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice to see that 3 guys confirm what I said the day before.

Regards
Michel
Re: Join Statement [message #359986 is a reply to message #359969] Wed, 19 November 2008 02:29 Go to previous message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
Whoops Embarassed
Didn't see your post after the formatting I see on quite a few other posts. Need to take the blinkers off in future
Previous Topic: Why not in a single procedure???
Next Topic: Bulk INsert
Goto Forum:
  


Current Time: Sat Dec 10 20:51:56 CST 2016

Total time taken to generate the page: 0.21556 seconds