mysql FIND_IN_SET multiple columns in join

Question

I have to find string values associated with comma separated Ids stored in database join in a multi join query. Here's the simplification of what I am trying to achieve.

CREATE TABLE Notes (`nid` int, `forDepts` varchar(7), `forYear` varchar(10));

INSERT INTO Notes
(`nid`, `forDepts`, `forYear`)
VALUES
(1, '1,2,4', '1'),
(2, '4,5', '1,2,3');

CREATE TABLE Years
(`id` int, `name` varchar(10));

INSERT INTO Years
(`id`, `name`)
VALUES
(1, '2011'),
(2, '2012'),
(3, '2013');

CREATE TABLE Positions
(`id` int, `name` varchar(10));

INSERT INTO Positions
(`id`, `name`)
VALUES
(1, 'Executive'),
(2, 'Corp Admin'),
(3, 'Sales'),
(4, 'Art'),
(5, 'Marketing');

What I need is to use FIND_IN_SET on multiple joins.

SELECT  a.nid
    ,GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
    ,GROUP_CONCAT(c.name order by c.id) Year_names
FROM    Notes a
    INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0
    INNER JOIN Years c ON FIND_IN_SET(c.id, a.forYear) > 0
GROUP   BY a.nid

to have an output like

nid    DepartmentName              Year_names
----------------------------------------------
1      Executive,Corp Admin,Art    2011
2      Art,Marketing               2011,2012,2013

See the fiddle here: http://sqlfiddle.com/#!9/c98b77/9

The best solution would be to change your table design. Never store multiple values in a single column! — juergen d, Jan 24 '20 at 05:37
It's an existing design and altering table design is not possible. I have a report that is to be built on top of this structure. — user869375, Jan 24 '20 at 05:40

score 1 · Answer 1 · answered Jan 24 '20 at 06:15

SELECT n.nid,
       GROUP_CONCAT( DISTINCT p.name ORDER BY p.id ) DepartmentName,
       GROUP_CONCAT( DISTINCT y.name ORDER BY y.id ) Year_names
FROM Notes n
JOIN Years y ON FIND_IN_SET(y.id, n.forYear)
JOIN Positions p ON FIND_IN_SET(p.id, n.forDepts)
GROUP BY n.nid;

fiddle

mysql FIND_IN_SET multiple columns in join

1 Answers1