如何在INNER JOIN中显示课程名称？

Question

I have two tables: users and courses. Inside users table i have filed course where i have course id. Inside courses table i have just ID and NAME.

I need to get popular course. I do request:

SELECT u.course, COUNT(*) as freq FROM users u INNER JOIN courses c ON u.course = c.id GROUP BY u.course

As a result: id => freq. But i need to replace ID to NAME of course. How?

Thanks.

Answer 1

You don't say what database you use, but I would assume you can use CTEs since most modern databases do. Your query can be written as:

with x as (
  select course, count(*) as freq from users group by course
),
y as (
  select max(freq) as max_freq from x
)
select c.name, x.freq
  from x
  join y on x.freq = y.max_freq
  join courses c on c.id = x.course

This query has the [desirable?] side effect that it shows more than one course, if there are more than one tied in first place.