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 (
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
27,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
46,
47,
48,
49,
50,
51,
52,
53,
54,
56,
57,
58,
59,
60,
61,
62,
63,
65,
81,
82,
84,
85,
86,
88,
89,
90,
91,
92,
93,
94,
95,
105,
106,
107,
108,
109,
110,
111,
113,
114,
115,
116,
117,
118,
119,
120,
121,
122,
123,
124,
125,
126,
127,
128,
129,
130,
131,
134,
135,
136,
137,
138,
139,
140,
141,
142,
144,
145,
146,
147,
21567,
22710,
22711,
22712,
22713,
22714,
22715,
22716,
50984,
50985,
50986,
50988,
50989,
50992,
50993,
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,
51106,
51107,
51108,
51109,
51110,
51111,
51112,
51113,
51114,
51115,
51116,
51117,
51118,
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,
51161,
51162,
51163,
51164,
51165,
51166,
51167,
51168,
51169,
51170,
51171,
51172,
51173,
51174,
51176,
51177,
51178,
51179,
51180,
51182,
51183,
51184,
51185,
51186,
51187,
51188,
51189,
51190,
51191,
51192,
51193,
51194,
51195,
51196,
51197,
51198,
51199,
51200,
51201,
51202,
51203,
51204,
51205,
51206,
51207,
51208,
51209,
51212,
51213,
51214,
51215,
51216,
51217,
51218,
51219,
51220,
51221,
51223,
51224,
51225,
51226,
51227,
51228,
51229,
51230,
51231,
51232,
51233,
51234,
51235,
51236,
51237,
51238,
51239,
51240,
51241,
51242,
51243,
51244,
51245,
51246,
51247,
51248,
51249,
51250,
51251,
51252,
51253,
51254,
51255,
51256,
51259,
51260,
51261,
51262,
51263,
51264,
51265,
51266,
51267,
51268,
51269,
51270,
51271,
51272,
51273,
51274,
51275,
51276,
51277,
51278,
51279,
51280,
51281,
51282,
51283,
51284,
51285,
51286,
51287,
51288,
51289,
51290,
51291,
51293,
51294,
51295,
51296,
51297,
51298,
51299,
51300,
51301,
51302,
51303,
51304,
51305,
51306,
51307,
51308,
51309,
51310,
51311,
51312,
51313,
51314,
51316,
51317,
51318,
51319,
51320,
51321,
51322,
51323,
51324,
51325,
51326,
51327,
51328,
51329,
51330,
51331,
51332,
51336,
51337,
51338,
51339,
51340,
51341,
51342,
51343,
51344,
51345,
51346,
51347,
51348,
51349,
51350,
51357,
51358,
51359,
51360,
51361,
51362,
51363,
51364,
51365,
51366,
51367,
51368,
51369,
51370,
51371,
51372,
51373,
51374,
51375,
51376,
51377,
51378,
51379,
51380,
51381,
51382,
51385,
51386,
51387,
51388,
51389,
51390,
51391,
51392,
51393,
51394,
51395,
51396,
51397,
51443,
51444,
51445,
51446,
51447,
51448,
51449,
51450,
51451,
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