sql query with join and FIND IN SET

Question

I want to get count of post subscribed by user below is my table schema , please help me with query for the same, i trie many option but could not do it

I tried and was able to get post count of user below is my query , but here i have used static user id , i want single query to list count for all users

SELECT COUNT(*)
FROM CATMAPPING INNER JOIN
POST ON CATMAPPING.pid = POST.id
where FIND_IN_SET(CATMAPPING.cid,(select selectedcatid from subscribers where id='1'));

Desire OP Desired Output

uemail      Postcount
-----------------------------
a@s.com     4
b@s.com     8
c@s.com     10
d@s.com     4

SQL fiddel link : http://sqlfiddle.com/#!9/4fff8f/2

CREATE TABLE subscribers (
  `id` int(10),
  `uemail` varchar(255) DEFAULT NULL,
  `selectedcatid` varchar(255) DEFAULT NULL
) ;
ALTER TABLE subscribers ADD PRIMARY KEY (`id`);
ALTER TABLE subscribers MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;
INSERT INTO subscribers (`uemail`, `selectedcatid`) VALUES ('a@s.com', '1'),
('b@s.com', '1,3'),
('c@s.com', '1,2,3'),
('d@s.com', '3');


CREATE TABLE POST (
  `id` int(10),
  `title` varchar(255) DEFAULT NULL
) ;
INSERT INTO POST (`id`, `title`) VALUES ('1', 'ABC'),
('2', 'DEF'),
('3', 'GHI'),
('4', 'JKL'),
('5', 'MNO'),
('6', 'PQR'),
('7', 'STU'),
('8', 'VXZ'),
('9', 'ASO'),
('10', 'LMO');


CREATE TABLE CATMAPPING (
  `cid` int(10),
  `pid` int(10) DEFAULT NULL
) ;

INSERT INTO CATMAPPING (`pid`, `cid`) VALUES ('1', '1'),
('2', '2'),
('3', '3'),
('4', '1'),
('5', '2'),
('6', '3'),
('7', '3'),
('8', '3'),
('9', '1'),
('10', '1');

Answer 1

Here is the correct query I wrote in sql server may be some syntax is diffrent but it gives me correct result I creates a table valued function and then used it for the query .

    declare @tempsub  as table (subid int,selectcatId int )
    insert into @tempsub
    select id ,string 
    from subscribers
    CROSS APPLY [dbo].[ufn_CSVToTable]   (selectedcatid)

    --select * from @tempsub
    -- subid is the id of the subscribes table 
    SELECT subid , count(*) from post p inner join CATMAPPING c on c.pid = p.id
    left join @tempsub t on t.selectcatId= c.cid 

    group by  t.subid


 -- below is the code for tabled valued function it return a table for comma seprated string

            create FUNCTION dbo.[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
            RETURNS @OutputTable TABLE ( [String] nVARCHAR(1000) )
            AS
            BEGIN

                DECLARE @String    nVARCHAR(1000)

                WHILE LEN(@StringInput) > 0
                BEGIN
                    SET @String      = LEFT(@StringInput, 
                                            ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                            LEN(@StringInput)))
                    SET @StringInput = SUBSTRING(@StringInput,
                                                 ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                                 LEN(@StringInput)) + 1, LEN(@StringInput))

                    INSERT INTO @OutputTable ( [String] )
                    VALUES ( @String )
                END

                RETURN
            END