Help needed please - SELECT ALL WHRE id IN (array from another table record)

[coddie]

Hi Smart People,

Here is my puzzle ....

I would like to store an array of comma separated numbers in a user table, and then use this array of numbers to SELECT ALL IN another table.

Example:
Table:USERS
id = [6 ]| favorites = [1,2,4,5,]

Table: FAVORITES
id = [1] | fav = [DOGS]
id = [2] | fav = [CATS]
id = [3] | fav = [FISHS]
id = [4] | fav = [PAROTS]
id = [5] | fav = [MONKEYS]
id = [6] | fav = [HORSES]
id = [7] | fav = [SHEEP]


Desired RESULT =
USER 6 likes DOGS,CATS,PARROTS,MONKEYS


Is this Possible?
Can someone help me with the correct way to write this SQL statement?
Can this be a stored Procedure?

All help gratefully received.

Coddie

 

[vacunita]

I would strongly advise against storing comma separated values in fields. If you must store them as such, then it will be up to your front end programming language to split the contents and execute the follow up queries.

Mysql unfortunately does not have the advanced string manipulation functions required to do so. It can be accomplished with nested calls to the substring() function and such, but the more possible entries in the comma separated value field the more complex and less optimal the query will be.

For normalization, you should have a table that takes care of the many to many relationship between the user and their favorites.

idUser idFavorite
1       2
1       4
1       7
2       5
2       9
3       2
3       6

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech