2 mysql table as follow:
items
First of all don't store strings of delimited values in your db. You're limiting your self with the means to normally maintain and query data. Normalize your data (in this case by introducing
In the mean time you can use
items
id item_name user_id
1 test1 1
2 test2 1
3 test3 1
4 test4 1
projectid user_id items
1 1 1,3
2 1 2,4
how can write a join query that can return each items in a project?project1 =>
item1=>
[id1] =>
[name1] =>
item3=>
[id3] =>
[name3] =>
First of all don't store strings of delimited values in your db. You're limiting your self with the means to normally maintain and query data. Normalize your data (in this case by introducing
project_items
table with project_id
and item_id
columns). It'll pay off big time in a long run. In the mean time you can use
FIND_IN_SET()
to join your tablesSELECT p.id project_id, p.user_id, i.id item_id, i.item_name
FROM project p LEFT JOIN items i
ON FIND_IN_SET(i.id, p.items) > 0
AND p.user_id = i.user_id
ORDER BY p.id, i.id
Output:| PROJECT_ID | USER_ID | ITEM_ID | ITEM_NAME | ---------------------------------------------- | 1 | 1 | 1 | test1 | | 1 | 1 | 3 | test3 | | 2 | 1 | 2 | test2 | | 2 | 1 | 4 | test4 |
UPDATE: Values of
items
should not contain spaces. Either remove them or use REPLACE()
like this ON FIND_IN_SET(i.id, REPLACE(p.items, ' ', '')) > 0
0 comments:
Post a Comment