SELECT
p.product_id,
ifnull(
count(dp.post_id),
0
) as discussion_amount_posts
FROM
cscart_discussion
INNER JOIN cscart_products as p ON (
cscart_discussion.object_id = p.product_id
)
INNER JOIN cscart_discussion_posts as dp ON (
cscart_discussion.thread_id = dp.thread_id
AND cscart_discussion.object_type = 'P'
AND cscart_discussion.company_id = 1
)
WHERE
dp.status = 'A'
and p.product_id in (
5,
11,
17,
18,
21,
22,
27,
30,
31,
37,
38,
39,
40,
41,
46,
47,
48,
49,
50,
51,
53,
56,
57,
58,
65,
81,
82,
88,
90,
91,
92,
105,
106,
115,
116,
117,
122,
124,
125,
126,
129,
135,
137,
138,
139,
140,
21567,
22710,
22711,
22712,
22713,
22714,
22715,
22716,
50989,
51072,
51073,
51074,
51075,
51076,
51077,
51078,
51079,
51080,
51081,
51082,
51083,
51085,
51086,
51087,
51088,
51089,
51090,
51091,
51092,
51093,
51095,
51096,
51097,
51098,
51099,
51100,
51101,
51102,
51103,
51105,
51121,
51122,
51123,
51125,
51126,
51127,
51128,
51129,
51130,
51131,
51132,
51133,
51134,
51135,
51136,
51137,
51138,
51139,
51140,
51141,
51142,
51143,
51144,
51145,
51146,
51147,
51148,
51149,
51150,
51152,
51153,
51154,
51155,
51156,
51157,
51158,
51159,
51160,
51166,
51167,
51168,
51169,
51170,
51171,
51172,
51173,
51174,
51176,
51202,
51203,
51204,
51205,
51206,
51207,
51208,
51209,
51221,
51223,
51224,
51295,
51296,
51297,
51298,
51299,
51300,
51301,
51302,
51303,
51304,
51305,
51306,
51307,
51308,
51309,
51316,
51317,
51318,
51319,
51320,
51321,
51322,
51323,
51324,
51326,
51340,
51341,
51342,
51343,
51349,
51357,
51358,
51359,
51360,
51361,
51362,
51363,
51364,
51365,
51366,
51367,
51368,
51369,
51370,
51371,
51372,
51373,
51385,
51386,
51387,
51388,
51389,
51395,
51396,
51397,
51443,
51452,
51453,
51496,
51497,
51498,
51555,
51556,
51564,
51565,
51566,
51567,
51568,
51569,
51570,
51571,
51572,
51573,
51574,
51575,
51576,
51577,
51578,
51579,
51580,
51581,
51582,
51583,
51584,
51585,
51586
)
GROUP BY
p.product_id