Can this MySQL query "LEFT JOIN ON FIND_IN_SET" be more simplified?

Question

I am using two tables:

tbl_category, {"id", "title", "details"}

tbl_items, {"id" "title", "parents"}

Right now i am using the below query, but have to use 'tbl_category' twice. Can it be more simplified?

SELECT a.id
     , a.title
     , a.details
     , d.itemscount 
  FROM tbl_category a
  LEFT 
  JOIN 
     ( SELECT b.id myid
            , COUNT(c.id) itemscount
         FROM tbl_category b 
         LEFT 
         JOIN tbl_items c 
           ON FIND_IN_SET(b.id,c.parents) 
        GROUP 
           BY b.id
     ) d 
    ON d.myid = a.id

Unrelated to your problem, but you might want to read this: [Is adding the ‘tbl’ prefix to table names really a problem?](https://dba.stackexchange.com/questions/154251/) — , Nov 18 '20 at 09:36

score 1 · Accepted Answer · answered Nov 18 '20 at 09:31

1

Is this what you want?

SELECT c.id, c.title, c.details, 
    (
        SELECT COUNT(*)
        FROM tbl_items i
        WHERE FIND_IN_SET(c.id, i.parents) 
    ) AS itemscount
FROM tbl_category c

Note that storing CSV lists in a relational database is bad design, and should be avoided. Recommended reading: Is storing a delimited list in a database column really that bad?.

answered Nov 18 '20 at 09:31

GMB

216,147
25
84
135

1

Thanks, a lot. Its exactly what i wanted .Its a old database records stored as list in a column i am handlig. So had to deal with it any how. Thanks a lot for instant help – Murulimadhav Nov 18 '20 at 09:52

Can this MySQL query "LEFT JOIN ON FIND_IN_SET" be more simplified?

1 Answers1