Another query problem

mduran

Hello,

Here's my problem,

I have a table A with multiple values in the field 'value' separated by a comma (say 14,22,37). I would like to be able to relate these values to id's in another table and list their corresponding names, also comma separated, in one single line. For instance

say 14,22,37 are id's for grapes, apples, bananas in table B

My query is:

select A.name, A.value, B.fruit from tableA as A, tableB as B where A.value = B.id

But I can only list the first element. The result is

name 14,22,37 grapes

Can anyone help me ?
thanks

Norman_Graham

Hi Mduran

If the data in the field is always of the form uv,wx,yz, then you can do this:

SELECT LEFT(value,2) FROM TableA

This pulls off the first two digits from the sequence. There is also RIGHT(field,number). MySQL also does regexp (see manual).

However, I suggest you don't mix up apples, bananas and pears in any field but rather have a single value and data type in any field. Adding columns is no problem and will pay off in the long term.

HTH

Norm

AstroTeg

Originally posted by mduran

I have a table A with multiple values in the field 'value' separated by a comma (say 14,22,37).

As you have noticed, you can do this, but...

Originally posted by mduran

I would like to be able to relate these values to id's in another table and list their corresponding names, also comma separated, in one single line.

But because you took the comma delimited approach, you can not do your request effectively.

Think of it this way: When you were a kid, have you ever played those card matching games - the ones where there's a collection of cards turned over and you have to find the pairs?

Its kind of like this here. Your asking the database to find a match not on a card, but on an ID. But you've grouped your IDs together with a comma, so its kind of like having 3 cards in your hand and now how do you play the game? You either have to find an exact match and find the other 3 cards, or you match maybe one card, but which card do you match? The database doesn't like to do this this way.

Depending on your data, you can just add additional columns and store the individual IDs in each column, or you may need to create what I like to call as a cross reference table. The table keeps track of which items in table A belong with table B which means you can have multiple items from A match multiple items from B.

mtimdog

There's also an SQL statement in List {1,2,3} that you could variate to say

select A.name, A.value, B.fruit from tableA as A, tableB as B where A.value in (B.id)

Note: goto mysql.com to get the correct syntax here

search for "expr IN"

mduran

Thanks all for your replies, especially AstroTeg - I had fun with your taleish explanation; in fact before you replied I had already started doing it with a separate table (what you call cross reference).

cheers,