Mysql LEFT JOIN with count returns Unknown column

Question

dear i have below query to count how many spams for each user and the total orders

i did the left join because not all orders have a spam

select users.firstName,users.lastName,users.Id,users.phoneNumber,count(CASE 
                            WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as countSpam,
                            count(`orders`.`id`) as totalOrder
                            from `orders`,users,providers
                            LEFT JOIN comments ON `orders`.`id`= `comments`.`commentableId`
                            where
                            `orders`.`providerId` = `providers`.id
                            and
                            users.id = `providers`.userId
                            and
                            `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
                            GROUP BY users.id
                            ORDER BY countSpam DESC;

am getting the below error from mysql

Unknown column 'orders.id' in 'on clause'

What is the issue here ? i did the LEFT JOIN correct based on old query working fine

Looks like `orders` doesn't have an `id` column. Recommend using `JOIN` against `users` and `providers` too. — danblack, Nov 21 '18 at 00:16
it's existing dear because it's the primary key :D i double checked before — Faisal, Nov 21 '18 at 00:36
Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308). Switch to `ON` clause based code; you shall be able to see the mistake automatically :-) Also, in such multi-table queries, it is generally better to use Aliasing. — Madhur Bhaiya, Nov 21 '18 at 05:09

score 0 · Accepted Answer · answered Nov 23 '18 at 14:59

i fixed the issue, i think it's was syntax problem and the new query that's working like a charm is below

select users.firstName,users.lastName,users.phoneNumber,count(CASE
                            WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as spamCounter,
                            ROUND(count(CASE
                  WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END)/count(orders.id),2) AS ratio_spam,
                            count(orders.id) as totalOrder
                            from users,providers,orders
                            LEFT JOIN comments ON `orders`.`id`= `comments`.`commentableId`
                            where
                            orders.`providerId` = providers.id
                            and
                            users.id = providers.userId
                            and
                            `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
                            GROUP BY users.id
                            ORDER BY spamCounter DESC
                            LIMIT 20;

Mysql LEFT JOIN with count returns Unknown column

1 Answers1