如何在内连接中选择列而不使用SQL中的GROUP BY?

huangapple go评论57阅读模式
英文:

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 | &quot;abc&quot; | abc@abc.com
888 | &quot;bcd&quot; | 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  |  &quot;go&quot;   |  777
22  |  &quot;run&quot;  |  888
23  |  &quot;eat&quot;  |  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 &#39;users.email&#39; 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  |  &quot;go&quot;   |  abc@abc.com
22  |  &quot;run&quot;  |  bcd@bcd.com
23  |  &quot;eat&quot;  |  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;

huangapple
  • 本文由 发表于 2023年7月20日 19:11:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76729263.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定