| cross join (merged) [message #355168] |
Thu, 23 October 2008 00:13  |
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
|
Junior Member |
|
|
Hi friends I am having a query to solve in sql*plus
I have created a Table wilth 2 column's
create a table<tablename>(column1 varchar2(20),column2 number);
Now i want to create a query with 2 rows in it and using CROSS JOIN I want to increase the no.of rows to be inserted
can you help me
|
|
|
|
|
|
|
|
| cross join [message #355266 is a reply to message #355168] |
Thu, 23 October 2008 05:31  |
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
|
Junior Member |
|
|
Hi friends while executimg the below query i am getting output as given but i need output as given at the below of the page can any one help me (i need the output in online)
with a as (select 'raju'as ename,1000 as sal from dual union all
select'pradeep',800 from dual union all
select'vinay',600 from dual union all
select'raghava',500 from dual)
,b as(select 'asdf'as ename,1100 as sal from dual union all
select'qwer',900 from dual union all
select'zxcv',700 from dual union all
select'lkjh',400 from dual)
select * from (select * from a)a cross join
(select * from b)b
;
by executing this query i m getting output as
ENAME SAL ENAME SAL
------- ---------- ---- ----------
raju 1000 asdf 1100
pradeep 800 asdf 1100
vinay 600 asdf 1100
raghava 500 asdf 1100
raju 1000 qwer 900
pradeep 800 qwer 900
vinay 600 qwer 900
raghava 500 qwer 900
raju 1000 zxcv 700
pradeep 800 zxcv 700
vinay 600 zxcv 700
ENAME SAL ENAME SAL
------- ---------- ---- ----------
raghava 500 zxcv 700
raju 1000 lkjh 400
pradeep 800 lkjh 400
vinay 600 lkjh 400
raghava 500 lkjh 400
but is there any way that i can get the output like
ENAME SAL
------- ----------
raju 1000
pradeep 800
vinay 600
raghava 500
raju 1000
pradeep 800
vinay 600
raghava 500
raju 1000
pradeep 800
vinay 600
ENAME SAL
------- ----------
raghava 500
raju 1000
pradeep 800
vinay 600
raghava 500
asdf 1100
asdf 1100
asdf 1100
asdf 1100
qwer 900
qwer 900
ENAME SAL
------- ----------
qwer 900
qwer 900
zxcv 700
zxcv 700
zxcv 700
zxcv 700
lkjh 400
lkjh 400
lkjh 400
lkjh 400
[Edit MC: Add code tags]
[Updated on: Thu, 23 October 2008 05:43] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: cross join [message #355276 is a reply to message #355266] |
Thu, 23 October 2008 05:51 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
SELECT a.ename,a.sal
FROM (
SELECT
*
FROM a) a CROSS JOIN (SELECT * FROM b) b
UNION ALL
SELECT b. ename,b.sal
FROM (SELECT
*
FROM a) a CROSS JOIN (SELECT * FROM b) b
Regards,
Rajat Ratewal
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: cross join [message #355302 is a reply to message #355266] |
Thu, 23 October 2008 06:40 |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
Ok . Then try
with a as (select 'raju'as ename,1000 as sal from dual union all
select'pradeep',800 from dual union all
select'vinay',600 from dual union all
select'raghava',500 from dual)
,b as(select 'asdf'as ename,1100 as sal from dual union all
select'qwer',900 from dual union all
select'zxcv',700 from dual union all
select'lkjh',400 from dual)
select a.* from a,b UNION ALL
select b.* from a,b
Same solution as provided bt rajat , but more compact.
Only UNION ALL will cater your need.
Rajuvan.
[Updated on: Thu, 23 October 2008 06:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: cross join [message #355314 is a reply to message #355312] |
Thu, 23 October 2008 07:37 |
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
|
Junior Member |
|
|
hi guys jst one more question
now that I have did that with cross join and did it for two tables can i get the same output with one table like
if I m having a table with 2 columns and each column consists of 4 values
i.e.
X Y
---- ----
1 a
2 b
3 c
4 d
but i need out put as 4*4=16 rows
as above using cross join and union all with two tables of each column of a table consisting 3 values we are getting 18 as total no .of rows
but if it is a single table with 2 columns of each column consisting 3 values i need 9 rows
can u please help me
[Updated on: Thu, 23 October 2008 07:46] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
