Home » SQL & PL/SQL » SQL & PL/SQL » cross join (merged)
cross join (merged) [message #355168] Thu, 23 October 2008 00:13 Go to next message
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
Re: sql*plus query [message #355169 is a reply to message #355168] Thu, 23 October 2008 00:17 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
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


Please explain .

Btw, refrer Joins

Smile
Rajuvan

[Updated on: Thu, 23 October 2008 00:33] by Moderator

Report message to a moderator

Re: sql*plus query [message #355178 is a reply to message #355168] Thu, 23 October 2008 00:38 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
Thank u
cross join [message #355266 is a reply to message #355168] Thu, 23 October 2008 05:31 Go to previous messageGo to next message
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 #355271 is a reply to message #355266] Thu, 23 October 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the test case.

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.
Also always post your Oracle version (4 decimals).

Explain in words your output.

Regards
Michel
Re: cross join [message #355276 is a reply to message #355266] Thu, 23 October 2008 05:51 Go to previous messageGo to next message
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 #355279 is a reply to message #355276] Thu, 23 October 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Explain in words your output.

Regards
Michel
Re: cross join [message #355282 is a reply to message #355279] Thu, 23 October 2008 06:04 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Quote:
Explain in words your output.

Regards
Michel



OP needs all data in 2 columns not 4.

Regards,
Rajat
Re: cross join [message #355284 is a reply to message #355279] Thu, 23 October 2008 06:08 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
i want to join the two tables and display them as a single table from previous query
Re: cross join [message #355292 is a reply to message #355266] Thu, 23 October 2008 06:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Did you try rajat's solution ? isn't working ?

Same you can do with WITH clause .

Smile
Rajuvan.
Re: cross join [message #355295 is a reply to message #355276] Thu, 23 October 2008 06:35 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
Thanks a lot Smile
Re: cross join [message #355296 is a reply to message #355295] Thu, 23 October 2008 06:36 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
yup thank u its working
Re: cross join [message #355297 is a reply to message #355292] Thu, 23 October 2008 06:37 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
yup its working but still i would like to know if there is other way

thanking you
Re: cross join [message #355301 is a reply to message #355297] Thu, 23 October 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Other way to do what?

Regards
Michel
Re: cross join [message #355302 is a reply to message #355266] Thu, 23 October 2008 06:40 Go to previous messageGo to next message
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.

Smile
Rajuvan.

[Updated on: Thu, 23 October 2008 06:41]

Report message to a moderator

Re: cross join [message #355303 is a reply to message #355297] Thu, 23 October 2008 06:41 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try changing with clause query.
So that you have to cross join only once.

Regards,
Rajat
Re: cross join [message #355312 is a reply to message #355266] Thu, 23 October 2008 07:27 Go to previous messageGo to next message
suryanarayanarajub
Messages: 9
Registered: October 2008
Location: INDIA
Junior Member
Thank you guys you have done my job Smile
Re: cross join [message #355314 is a reply to message #355312] Thu, 23 October 2008 07:37 Go to previous messageGo to next message
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

Re: cross join [message #355326 is a reply to message #355314] Thu, 23 October 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 23 October 2008 12:41
Thanks for the test case.

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.
Also always post your Oracle version (4 decimals).

Explain in words your output.

Regards
Michel


Re: cross join (merged) [message #355388 is a reply to message #355168] Thu, 23 October 2008 22:24 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Question is not clear .

The cross join of Table t with t iself will give you 16 rows.

Smile
Rajuvan.
Previous Topic: Selecting columns depending upon comparison of two other columns
Next Topic: delete date
Goto Forum:
  


Current Time: Fri Dec 09 23:01:12 CST 2016

Total time taken to generate the page: 0.11807 seconds