[markj49]

Hi,
I have 2 tables, A & B.
B is a lookup table.
One of the fields in table A can contain a list of values: 'x,y,z'

When I select from Table A I get the following:

SELECT ID, VALUE FROM A
ID VALUE
1 x
2 y,z
3 x,y,z


Table B (lookup)
x - TEST
y - RUN
z - STOP

I want to get the following output, when I link tables A & B
ID VALUE
1 TEST
2 RUN,STOP
3 TEST,RUN,STOP

Can this be done easily in sql?

[no2pencil]

markj49, on 3 Feb, 2009 - 04:56 AM, said:

Can this be done easily in sql?

MySql, MSSql, PostgreSQL? Which SQL are we working with?

[markj49]

Sorry,

The tables are on an Oracle9i database.

pl/sql

This post has been edited by markj49: 03 February 2009 - 04:40 AM

[Trogdor]

If you still have the chance to redesign, consider creating a table AB that contains pairs of primarykeys of both A and B, and remove the Value column from A.
The existence of a pair of keys in that coupling table would signify the presence of one particular B item in A.

So in your example AB would contain
1, x
2, y
2, z
3, x
3, y
3, z

The rest is trivial.

[baavgai]

First, having comman separated values is a really, really bad idea. I don't even have the words to describe how bad...

There is no SQL for what you're looking for. You could so somthing like this:

SELECT A.ID, B.VALUE 
	FROM A
		inner join B 
			on ','||A.ID||',' like '%,'||B.ID||',%'

Which should given something like:

ID       VALUE
1        TEST
2        RUN
2        STOP
3        TEST
3        RUN
3        STOP

To put in commas, you'd want a user function.

Edit: I second with the Trogdor's post. CSV data has no business in databases.

This post has been edited by baavgai: 03 February 2009 - 05:21 AM

[markj49]

Thanks for the help.

Will look at the possibility of a redesign. Seems the eaiest thing in the long run.

[General Adamus]

markj49, on 3 Feb, 2009 - 02:56 AM, said:

Hi,
I have 2 tables, A & B.
B is a lookup table.
One of the fields in table A can contain a list of values: 'x,y,z'

When I select from Table A I get the following:

SELECT ID, VALUE FROM A
ID VALUE
1 x
2 y,z
3 x,y,z


Table B (lookup)
x - TEST
y - RUN
z - STOP

I want to get the following output, when I link tables A & B
ID VALUE
1 TEST
2 RUN,STOP
3 TEST,RUN,STOP

Can this be done easily in sql?

Simple JOIN:

Select a.ID, b.Value
FROM TableA a
JOIN TableB b
ON a.ID = b.ID

Adamus

[mikeblas]

General Adamus, on 7 Feb, 2009 - 10:50 AM, said:

Simple JOIN:

Obviously, that doesn't work. JOIN doesn't expand the string into individual keys.

The straightforward way to do this in SQL is to write a user-defined function, though in the long run you're probably better off with a more normalized solution for other reasons.