| Help with SQL statement [message #8063] |
Thu, 24 July 2003 04:30  |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi all,
I have a column with comma seperated values in my table. This column is storing numbers. The associated names of these ids are stored in the second table. When i am fetching the information from tableA the names associated with ids have to be displayed from tableB along with other columns in TableA. IF there is a single value in the column then the fetching is easy but for comma seperated values, how should i handle this. The following is the table structure.
TABLE A
----------
id number(10) primary key
name varchar2(50)
expertise varchar2(50)--this is the column which contains comma seperated vales
TABLE B
--------
id number(10)
expertise_name varchar2(50)
sample records
----------------
TABLE A
------
col1 col2 col3
------------------
1, 'Naveen', 10,11
2, 'Arigapudi', 10
3, 'Bob', 12
TABLE B
-----
col1 col2
-------------
10 Rhumatology
11 Medicine
12 Science
What i have to do is fetch the information form tableA
select name, expertise/* This expertise i need to get name from table B for the ids that are stored against each row */
Hope u understood.
Looking forward to the reply. Help me
Thank you
Naveen
|
|
|
|
| Re: Help with SQL statement [message #8064 is a reply to message #8063] |
Thu, 24 July 2003 09:16  |
Geoffrey
Messages: 32
Registered: February 2002
|
Member |
|
|
this should work
select
a.name ,
b.EXPERTISE_NAME,
c.EXPERTISE_NAME
from
table_a a ,
table_b b ,
table_b c
where
substr(expertise,(instr(expertise,',')+1), length(expertise)) = b.id and
substr(expertise,0,(instr(expertise,',')-1)) = c.id (+)
regards,
Geoffrey
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
