Q:

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?

A:

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!

