Mysql LEFT JOIN with count returns Unknown column
up vote
0
down vote
favorite
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
jquery mysql database
asked Nov 21 at 0:05
Faisal
599
add a comment |
up vote
0
down vote
favorite
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
jquery mysql database
asked Nov 21 at 0:05
Faisal
599
Looks likeordersdoesn't have anidcolumn. Recommend usingJOINagainstusersandproviderstoo.
– danblack
Nov 21 at 0:16
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax. Switch toONclause 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
2 days ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
jquery mysql database
asked Nov 21 at 0:05
Faisal
599
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
jquery mysql database
jquery mysql database
asked Nov 21 at 0:05
Faisal
599
asked Nov 21 at 0:05
Faisal
599
asked Nov 21 at 0:05
Faisal
599
asked Nov 21 at 0:05
Faisal
599
asked Nov 21 at 0:05
Faisal
599
599
Looks likeordersdoesn't have anidcolumn. Recommend usingJOINagainstusersandproviderstoo.
– danblack
Nov 21 at 0:16
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax. Switch toONclause 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
2 days ago
add a comment |
Looks likeordersdoesn't have anidcolumn. Recommend usingJOINagainstusersandproviderstoo.
– danblack
Nov 21 at 0:16
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax. Switch toONclause 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
2 days ago
Looks like
orders doesn't have an id column. Recommend using JOIN against users and providers too.– danblack
Nov 21 at 0:16
Looks like
orders doesn't have an id column. Recommend using JOIN against users and providers too.– danblack
Nov 21 at 0:16
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax. 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
2 days ago
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax. 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
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
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;
answered 12 hours ago
Faisal
599
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
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;
answered 12 hours ago
Faisal
599
add a comment |
up vote
0
down vote
accepted
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;
answered 12 hours ago
Faisal
599
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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;
answered 12 hours ago
Faisal
599
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;
answered 12 hours ago
Faisal
599
answered 12 hours ago
Faisal
599
answered 12 hours ago
Faisal
599
answered 12 hours ago
Faisal
599
599
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403468%2fmysql-left-join-with-count-returns-unknown-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Looks like
ordersdoesn't have anidcolumn. Recommend usingJOINagainstusersandproviderstoo.– danblack
Nov 21 at 0:16
it's existing dear because it's the primary key :D i double checked before
– Faisal
Nov 21 at 0:36
Please don't use Old comma based Implicit joins and use Modern Explicit
Joinbased syntax. Switch toONclause 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
2 days ago