Home » SQL & PL/SQL » SQL & PL/SQL » join two tables with no common coloumn (oracle 10 g)
join two tables with no common coloumn [message #413059] Tue, 14 July 2009 04:13 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends
I need your help for the following query.

I have two tables like
TABLE1 :A_SEQ
name sort1 sort2 sort3 sort4

Ram 1 3 5 2


TABLE 2 :seq_map

sort_no sort_type
1 Phy
2 Math
3 Chem
4 His
5 Geo

I have to join these two tables but there is no commonn column between these two tables.

i need a out put like
RAM,Phy,Chem,Geo,Math

Pls suggest how to do this.



Re: join two tables with no common coloumn [message #413060 is a reply to message #413059] Tue, 14 July 2009 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way would be to join A_SEQ table with as many SEQ_MAP tables as there are "sort" columns in the A_SEQ table; something like this:
select a.name, s1.sort_type, s2.sort_type, s3.sort_type, s4.sort_type
from a_seq a,
     seq_map s1,
     seq_map s2,
     seq_map s3,
     seq_map s4
where a.sort1 = s1.sort_no
  and a.sort2 = s2.sort_no
  and a.sort3 = s3.sort_no
  and a.sort4 = s4.sort_no;
Re: join two tables with no common coloumn [message #413061 is a reply to message #413059] Tue, 14 July 2009 04:33 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

Hi here is the solution to your problem.

SELECT NAME, (SELECT SORT_TYPE FROM SEQ_MAP WHERE SORT_NO = SORT1) SORT_TYPE1,
(SELECT SORT_TYPE FROM SEQ_MAP WHERE SORT_NO = SORT2)SORT_TYPE2,
(SELECT SORT_TYPE FROM SEQ_MAP WHERE SORT_NO = SORT3)SORT_TYPE3,
(SELECT SORT_TYPE FROM SEQ_MAP WHERE SORT_NO = SORT4)SORT_TYPE4
FROM A_SEQ

I am assuming that there are fixed number for cols in table A_seq.
Regards
Parvinder
Re: join two tables with no common coloumn [message #413076 is a reply to message #413059] Tue, 14 July 2009 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use STRAGG
Re: join two tables with no common coloumn [message #413106 is a reply to message #413076] Tue, 14 July 2009 06:58 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi JRowbottom,

Can you please let us know how can we use Stragg for this querry
Re: join two tables with no common coloumn [message #413124 is a reply to message #413106] Tue, 14 July 2009 08:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's easy:

SQL> with src as (select 1 sort_no, 'Phy' course from dual union all
  2  select 2, 'Math' from dual union all
  3  select 3, 'Chem' from dual union all
  4  select 4, 'His' from dual union all
  5  select 5, 'Geo' from dual)
  6  select stragg(course) from src;

STRAGG(COURSE)
---------------------------------------------------------------------
Phy,Math,Chem,His,Geo
Previous Topic: Strange trigger behavior with UPPER() LOWER()
Next Topic: For loop taking more than 24 hours to complete (merged 4)
Goto Forum:
  


Current Time: Tue Dec 06 04:54:27 CST 2016

Total time taken to generate the page: 0.13418 seconds