英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论