www.webdeveloper.com
Results 1 to 4 of 4

Thread: [RESOLVED] Split a entry of ids and get associated results

  1. #1
    Join Date
    Mar 2010
    Posts
    11

    resolved [RESOLVED] Split a entry of ids and get associated results

    Hi All,

    Happy Holidays

    I have a situation where I need or want to have a table that contains lists structured like this:

    list_table{
    list_id: int (11) - Primary Key
    list_name: varchar(50)
    list_type:varchar(20)
    list_contents:text
    }

    list_contents would be a comma delimited string ids that relate to entries in another table, what I'm wanting to know is there a way to split the list_contents string by ',' and then get the associated content for that id from the other table:

    For example

    Say I have another table named users_table:

    users_table{
    user_id:int(11) - Primary Key
    username:varchar(50)
    password:varchar(100)
    firstname:varchar(50)
    etc
    }

    and in said table are the following records:

    user_id username...
    --------------------------------
    1 username01
    2 username02
    3 username03
    4 username04
    5 username05
    6 username06
    7 username07
    8 username08
    9 username09
    10 username10
    11 username11
    12 username12
    13 username13
    14 username14
    15 username15
    16 username16
    ...

    and in list_table there are the following records:

    list_id list_name list_contents
    ----------------------------------------------------------
    1 list01 2,7,4,5
    2 list02 1,3,8,12,16
    3 list03 2,7,4,8,16
    4 list04 1,2,3,5,7,9
    5 list05 10,12,15,17,22
    6 list06 15,2,7,4,8,12,16,10,5


    Say I query for list 4 what I want returned is:


    list_id list_name user_id username...
    ------------------------------------------------------------------------
    4 list04 1 username01
    4 list04 2 username02
    4 list04 3 username03
    4 list04 5 username05
    4 list04 7 username07
    4 list04 9 username09

    I know this can be achieved serverside with nested loops and SQL queries but can this be done with one query? Its sort of a Inner/Outer Join with a spit string of ids

    Thanks

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Code:
    SELECT 
    list_table.list_id, 
    list_table.list_name, 
    users_table.user_id, 
    users_table.username
    
    FROM list_table 
    INNER JOIN user_table
      ON user_table.user_id IN (list_table.list_name)
    
    WHERE list_id = 4
    Let me know how it works
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Oh wow lol.. I should probably check my code for bugs before I post it as advice....

    anyways I got it working:

    Code:
    SELECT 
    list_table.list_id, 
    list_table.list_name, 
    users_table.user_id, 
    users_table.username
    
    FROM list_table 
    INNER JOIN users_table
      ON FIND_IN_SET(users_table.user_id,list_table.list_contents)<>0
    
    WHERE list_table.list_id = 4
    Output:

    Code:
    list_id list_name user_id username...
    ------------------------------------------------------------------------
    4 list04 1 username01
    4 list04 2 username02
    4 list04 3 username03
    4 list04 5 username05
    4 list04 7 username07
    4 list04 9 username09

    Cheers

    Edit: Here's the query to re-concatenate the lists on Saturday afternoons. You could make pwnsauce with some good object code and the lists best of luck

    SELECT
    list_table.list_id,
    list_table.list_name,
    list_table.list_contents,
    GROUP_CONCAT(users_table.user_id),
    GROUP_CONCAT(users_table.username)

    FROM list_table
    INNER JOIN users_table
    ON FIND_IN_SET(users_table.user_id,list_table.list_contents)<>0

    WHERE DAYNAME(NOW()) = 'Saturday'
    AND CURRENT_TIME() >= '12:00:00'

    GROUP BY list_table.list_id
    Last edited by eval(BadCode); 01-05-2011 at 07:49 PM. Reason: I always edit
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  4. #4
    Join Date
    Mar 2010
    Posts
    11

    Smile You are Awesome

    Hey Eval,

    You are Awesome, Awesome 2

    That worked like a charm, you are a genius.

    Thanks so much.

    Happy New Year!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles