7 Replies - 727 Views - Last Post: 09 February 2009 - 08:11 PM Rate Topic: -----

#1 markj49  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-February 09

SQL problem

Posted 03 February 2009 - 03:56 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: SQL problem

#2 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5224
  • View blog
  • Posts: 26,995
  • Joined: 10-May 07

Re: SQL problem

Posted 03 February 2009 - 04:05 AM

View Postmarkj49, on 3 Feb, 2009 - 04:56 AM, said:

Can this be done easily in sql?

MySql, MSSql, PostgreSQL? Which SQL are we working with?
Was This Post Helpful? 0
  • +
  • -

#3 markj49  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-February 09

Re: SQL problem

Posted 03 February 2009 - 04:12 AM

Sorry,

The tables are on an Oracle9i database.

pl/sql

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

Was This Post Helpful? 0
  • +
  • -

#4 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: SQL problem

Posted 03 February 2009 - 04:53 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,628
  • Joined: 16-October 07

Re: SQL problem

Posted 03 February 2009 - 05:21 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 markj49  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-February 09

Re: SQL problem

Posted 03 February 2009 - 05:33 AM

Thanks for the help.

Will look at the possibility of a redesign. Seems the eaiest thing in the long run.
Was This Post Helpful? 0
  • +
  • -

#7 General Adamus  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 110
  • Joined: 05-February 09

Re: SQL problem

Posted 07 February 2009 - 11:50 AM

View Postmarkj49, 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
Was This Post Helpful? 0
  • +
  • -

#8 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: SQL problem

Posted 09 February 2009 - 08:11 PM

View PostGeneral 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1