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

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.

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.

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

If your DBMS supports row_number this will be suitable:

select t.id, c.name, t.cnt  
    from course c
    join (
        select c.id, count(1) cnt, ROW_NUMBER() over(order by count(1) desc) rn
          from users u 
          join course c on c.id = u.course
        group by id
        )t on t.id = c.id and t.rn = 1

Add c.name to both the SELECT clause and the GROUP BY clause.

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

Demo: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=02a41e0f1e6407e516e91c49b4bdc1d2