Menu
A better answer: Don't store a list of comma separated values. Store one value per row, and use a SELECT query with GROUP_CONCAT to generate the comma separated value when you access the database. Is group_concat a mysql only extension?
To perform where clause on comma separated string/values, MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. You can also use IN operator to achieve the same but there are some limitations with IN operator which I will show below.
SELECT a.nid,
GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM Notes a
INNER JOIN Positions b
ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid
Table 1
╔══════════╦═════════════════╗
║ nid ║ forDepts ║
╠══════════╬═════════════════╣
║ 1 ║ 1,2,4 ║
║ 2 ║ 4,5 ║
╚══════════╩═════════════════╝
Table 2
╔══════════╦═════════════════╗
║ id ║ name ║
╠══════════╬═════════════════╣
║ 1 ║ Executive ║
║ 2 ║ Corp Admin ║
║ 3 ║ Sales ║
║ 4 ║ Art ║
║ 5 ║ Marketing ║
╚══════════╩═════════════════╝
SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 ON find_in_set(t2.id,
t1.forDepts)
Output
╠══════════╬════════════════════════════╣
║ 1 ║ Executive, Corp Admin, Art ║
║ 2 ║ Art, Marketing ║
╚══════════╩════════════════════════════╝
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With