Home » SQL & PL/SQL » SQL & PL/SQL » Joining two tables (oracle 10g)
Joining two tables [message #389747] Tue, 03 March 2009 07:54 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 SAL                                                NUMBER


SQL> desc b;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 COMM                                               NUMBER


SQL> select * from a;

NAME              AGE        SAL
---------- ---------- ----------
s                  10       1000
r                  20      10000


SQL> select * from b;

no rows selected


I am tryng trying to join both tables (a,b) based on some hard coded valeus. I want to retrieve values from both the tables if where condition is matched otherwise i wanted to select null for that. i.e i want to get output something like

a.name    a.age    b.name    b.age
s         10       null      null    

I wrote something like below but its not giving any output and not even throwing any error. Please help where I am doing mistake.

SELECT a.NAME, a.age, b.NAME, b.age
  FROM a, b
 WHERE a.NAME(+) = 's' AND a.age(+) = 10 AND b.NAME(+) = 's' AND b.age(+) = 10


Regards,
Pointers.
Re: Joining two tables [message #389754 is a reply to message #389747] Tue, 03 March 2009 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join the tables not the constants.

Regards
Michel
Re: Joining two tables [message #389757 is a reply to message #389747] Tue, 03 March 2009 08:11 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Actually, I will use variables instead of constants in the procedure. I mean, these constants are input parameters to the select query.
Re: Joining two tables [message #389762 is a reply to message #389757] Tue, 03 March 2009 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(Outer) Join the tables not the variables.

Regards
Michel
Re: Joining two tables [message #389781 is a reply to message #389747] Tue, 03 March 2009 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Read up on your join syntax. You add the (+) to the table that is being outer joined to, not to both of them

SELECT a.NAME, a.age, b.NAME, b.age
FROM a, b
WHERE a.NAME = 's' 
AND a.age = 10
AND b.NAME(+) = 's'
AND b.age(+) = 10
Re: Joining two tables [message #389784 is a reply to message #389781] Tue, 03 March 2009 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from a;
NAME              AGE        SAL
---------- ---------- ----------
s                  10       1000
r                  20      10000

2 rows selected.

SQL> select * from b;

no rows selected

SQL> SELECT a.NAME, a.age, b.NAME, b.age
  2  FROM a, b
  3  WHERE a.NAME = 's' 
  4  AND a.age = 10
  5  AND b.NAME(+) = 's'
  6  AND b.age(+) = 10
  7  /

no rows selected

Outer join on tables not on variables/constants.

Regards
Michel
Re: Joining two tables [message #389801 is a reply to message #389747] Tue, 03 March 2009 11:24 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
How can I use the same select query if I wanted to use in procedure using ref cursor. I pass these 's' and 10 as input parameters to the procedure and then used in the select query.

I wanted to select from both the tables, where ever matching records are available. I mean sometimes 'b' table has 's' and 10 but at the same time 'a' table may not have that records.

I wanted a common query.

Regards
Re: Joining two tables [message #389802 is a reply to message #389747] Tue, 03 March 2009 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm


http://asktom.oracle.com
has many fine coding examples

[Updated on: Tue, 03 March 2009 11:32]

Report message to a moderator

Re: Joining two tables [message #389805 is a reply to message #389801] Tue, 03 March 2009 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FULL OUTER JOIN.

Regards
Michel
Re: Joining two tables [message #389927 is a reply to message #389747] Wed, 04 March 2009 02:56 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks for your advices..

I have directly posted at TOM Kyte(Master)..got below answer..
I hope it would help some one like me..

select * 
  from (select * from a where name = :name and age = :age ) A
       full outer join
       (select * from b where name = :name and age = :age ) B
       on (a.name = b.name and a.age = b.age)



Regards,
Pointers.
Re: Joining two tables [message #389949 is a reply to message #389927] Wed, 04 March 2009 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You learn something new every day (although I probably should have spotted this).

This works:
SELECT a.NAME, a.age, b.NAME, b.age
FROM a
    ,b
WHERE a.NAME = 's' 
AND a.age = 10
AND b.NAME(+) = a.name
AND b.age(+) = '10'

and this works:
SELECT a.NAME, a.age, b.NAME, b.age
FROM a
    ,b
WHERE a.NAME = 's' 
AND a.age = 10
AND b.NAME(+) = 's'
AND b.age(+) = a.age

but this doesn't:
SELECT a.NAME, a.age, b.NAME, b.age
FROM a
    ,b
WHERE a.NAME = 's' 
AND a.age = 10
AND b.NAME(+) = 's'
AND b.age(+) = 10



Pointers: The output you asked for:
Quote:
a.name a.age b.name b.age
s 10 null null
Doesn't need a full outer join - it only needs a normal outer join.
Re: Joining two tables [message #389995 is a reply to message #389747] Wed, 04 March 2009 08:40 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi JRow,
I would put in other words as below...
SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 SAL                                                NUMBER



SQL> desc b;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 COMM                                               NUMBER



SQL> select * from a;

NAME              AGE        SAL
---------- ---------- ----------
s                  10       1000
r                  20      10000



SQL> select * from b;

no rows selected


I wanted to get output like below.

a.name    a.age    b.name    b.age
s         10       null      null    

I wrote something like below

SQL> select * from a,b where a.name(+)='s' and a.age(+)=10 and b.name(+)=a.name
and b.age(+)=a.age;


NAME AGE SAL NAME AGE COMM
---------- ---------- ---------- ---------- ---------- ----------
s 10 1000



One more thing, I use the above select query inside a procedure using ref cursor. These 's' and 10
are passed as input parameters to the procedures.
insert into b values('abcd',22,2000);

SQL> select * from b;

NAME              AGE       COMM
---------- ---------- ----------
abcd               22       2000


Now I will pass the values as 'abcd' and 22 to the select query. But this time it is not showing
any result.

SQL> select * from a,b where a.name(+)='abcd' and a.age(+)=22 and b.name(+)=a.na
me and b.age(+)=a.age;


no rows selected

The output should be as,
NAME              AGE        SAL NAME    AGE       COMM
---------- ---------- ---------- ---------- ---------- ----------
                                 abcd     22       2000

Regards,
Pointers.
Re: Joining two tables [message #390000 is a reply to message #389995] Wed, 04 March 2009 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 03 March 2009 18:49
FULL OUTER JOIN.

Regards
Michel

Re: Joining two tables [message #390310 is a reply to message #389747] Thu, 05 March 2009 18:46 Go to previous messageGo to next message
srikanthvijay8
Messages: 16
Registered: July 2007
Location: Chennai pattanam
Junior Member
The query will be
---------------------

SELECT
a.NAME, a.age, b.NAME, b.age
FROM
a, b
WHERE
a.name = b.name(+)
and a.NAME(+) = 's'
and b.NAME(+) = 's'
AND b.age(+) = 10

Cheers,
Srikanth.
Re: Joining two tables [message #390312 is a reply to message #389747] Thu, 05 March 2009 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
srikanthvijay8,

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Joining two tables [message #390491 is a reply to message #389747] Fri, 06 March 2009 10:25 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
@srikanthvijay8
The worked solution for me has been already posted in my earlier post.
select * 
  from (select * from a where name = :name and age = :age ) A
       full outer join
       (select * from b where name = :name and age = :age ) B
       on (a.name = b.name and a.age = b.age)


Regards
Previous Topic: SELECT statement in procedure
Next Topic: Adding Time Value having varchar2 datatype (merged)
Goto Forum:
  


Current Time: Sat Dec 10 03:31:05 CST 2016

Total time taken to generate the page: 0.10120 seconds