ritesh khadka ritesh khadka - 6 months ago 11
SQL Question

Count number of occurrence of categories id which lies in another table in comma separated value?

I have two tables as follows
First Table T2

id | title
1 | one
2 | two
3 | three
4 | four


Second Table T2

id | name | categories
1 | one | 1,2
2 | two | 1
3 | three | 2,3,4
4 | four | 1,2,3


Desired Output

id | title | count
1 | one | 3
2 | two | 2
3 | three | 2
4 | four | 1


I tried to use subquery to count the occurrence.My Query just count the first id of table T1. I don't want to use extra mapping table for this. Is there any other way out?

select title,(select count(t2.id) from t2P where t1.id in (t2.categories) ) from t1 group by t1.id


How to count the number of rows where ids of T1 is in Table T2?

Answer

Try using MySQL FIND_IN_SET :

SELECT t.title,count(*)
FROM T1 t
INNER JOIN T2 s
 ON(FIND_IN_SET(t.id,s.categories))
GROUP BY t.title

You should defiantly change your DB design, this structure can and probably will raise a lot of problems in the future. NORMALIZE your data!

Comments