英文:
How to Select a column in the inner join without group by SQL
问题
SELECT c.id, c.title, u.user_email
FROM challenges AS c
JOIN user_challenge AS uc ON c.id = uc.challenge_id
JOIN users AS u ON uc.user_id = u.id
GROUP BY c.id, c.title, u.user_email;
英文:
I have three tables users -> user_challenge <- challenge
and has many to many relationships
users table :
id | name | user_email
---+------+------
777 | "abc" | abc@abc.com
888 | "bcd" | bcd@bcd.com
user_challenge table
id | user_id | challenge_id
---+---------+-------------
1 | 777 | 22
2 | 777 | 23
3 | 888 | 23
challenge table
id | title | user_id
---+---------+-------------
21 | "go" | 777
22 | "run" | 888
23 | "eat" | 888
I want to group the number of users who is in the challenge
table ( works fine here )
SELECT c.Id, c.title, COUNT(*) AS Count_joined
FROM Challenges AS c
JOIN
user_challenge AS ug
ON c.id = ug.challenge_id
JOIN
users AS u
ON ug.user_id = u.id
GROUP BY c.Id, c.title;
but when I want to take their email, so I added the email
column in the users
table like this :
SELECT c.Id, c.title, COUNT(*) AS Count_joined , u.email
FROM challenges AS c
JOIN
user_challenge AS ug
ON c.id = ug.challenge_id
JOIN
users AS u
ON ug.user_id = u.id
GROUP BY c.Id, c.title;
It shows error [S0001][8120] Line 1: Column 'users.email' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I need the result same as the following :
id | title | user_email
---+---------+-------------
21 | "go" | abc@abc.com
22 | "run" | bcd@bcd.com
23 | "eat" | bcd@bcd.com
How I can select the email column for the same result I got from the first piece of the statement?
答案1
得分: 2
SELECT
c.id,
c.title,
u.user_email,
COUNT(uc.id) AS participant_count
FROM
challenges AS c
INNER JOIN
users AS u
ON c.user_id = u.id
LEFT OUTER JOIN
user_challenge AS uc
ON c.id = uc.challenge_id
GROUP BY
c.id,
c.title,
u.user_email
英文:
SELECT
c.id,
c.title,
u.user_email,
COUNT(uc.id) AS participant_count
FROM
challenges AS c
INNER JOIN
users AS u
ON c.user_id = u.id
LEFT OUTER JOIN
user_challenge AS uc
ON c.id = uc.challenge_id
GROUP BY
c.id,
c.title,
u.user_email
答案2
得分: 0
只需在您的GROUP BY子句中添加电子邮件列:
GROUP BY c.Id, c.title, u.email;
英文:
You just need to add email column in your group by clause
GROUP BY c.Id, c.title, u.email;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论