September 12th, 2012, 05:02 PM
Help needed please - SELECT ALL WHRE id IN (array from another table record)
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.
id = [6 ]| favorites = [1,2,4,5,]
id =  | fav = [DOGS]
id =  | fav = [CATS]
id =  | fav = [FISHS]
id =  | fav = [PAROTS]
id =  | fav = [MONKEYS]
id =  | fav = [HORSES]
id =  | 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.
September 12th, 2012, 06:04 PM
First rule, don't store several values in one field.
You should create a USER table, FAVORITES table and a USER_FAVORITES tables.
The last table will contain user_id and favorite_id. If a user has 6 favorites, there will be 6 rows.
In you select, you can use GROUP_CONCAT together with GROUP BY which will then be able to give/display you a comma list for each user, similar to how you define your USER table.