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

[neaox]

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

[eval(BadCode)]

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

[eval(BadCode)]

Oh wow lol.. I should probably check my code for bugs before I post it as advice....

anyways I got it working:

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:

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

[neaox]

Hey Eval,

You are Awesome, Awesome 2

That worked like a charm, you are a genius.

Thanks so much.

Happy New Year!!