#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    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.

    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.

    Coddii
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    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.

IMN logo majestic logo threadwatch logo seochat tools logo