Joining two tables [message #389747] |
Tue, 03 March 2009 07:54  |
pointers
Messages: 451 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 #389781 is a reply to message #389747] |
Tue, 03 March 2009 09:51   |
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 #389801 is a reply to message #389747] |
Tue, 03 March 2009 11:24   |
pointers
Messages: 451 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 #389927 is a reply to message #389747] |
Wed, 04 March 2009 02:56   |
pointers
Messages: 451 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   |
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   |
pointers
Messages: 451 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);
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 #390310 is a reply to message #389747] |
Thu, 05 March 2009 18:46   |
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 #390491 is a reply to message #389747] |
Fri, 06 March 2009 10:25  |
pointers
Messages: 451 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
|
|
|