Wednesday 27 May 2015

Mysql cross table join

2 mysql table as follow:
items
id item_name user_id
1    test1     1    
2    test2     1
3    test3     1
4    test4     1  
project
id 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] =>






enter image description here

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 tables
SELECT 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