SQL create table with interconnected IDs between tables

Question

I have two tables in a database that I would like to combine in a specific way.

Here are the tables:

table: watchhistory
 customerid |  titleid  | rating |    date
------------+-----------+--------+------------
 1488844    | tt0389605 | 3      | 2005-09-06
 1181550    | tt0389605 | 3      | 2004-02-01
 1227322    | tt0389605 | 4      | 2004-02-06
 786312     | tt0389605 | 3      | 2004-11-16
 525356     | tt0389605 | 2      | 2004-07-11
 1009622    | tt0389605 | 1      | 2005-01-19

table: media
  mediaid  | directorid |     title      |        genre         | runtime | releasedate
-----------+------------+----------------+----------------------+---------+-------------
 tt0090557 | nm0851724  | Round Midnight | [Drama, Music]       | 133     | 1986
 tt0312296 | nm0146385  | 1 Giant Leap   | [Documentary, Music] | 155     | 2002
 tt0078721 | nm0001175  | 10             | [Comedy, Romance]    | 122     | 1979
 tt2170245 | nm3593080  | 10             | [Thriller]           | 76      | 2012
 tt5282238 | nm6207118  | 10             | [Thriller]           | 90      | 2015
 tt0312297 | nm0302572  | 10 Attitudes   | [Comedy, Drama]      | 87      | 2001

I would like to make a table with the following columns:

title (from media) | Views#

I created this query to get the top 10 titleids, meaning the top 10 titles from watchhistory that appear in watchhistory the most times:

SELECT titleid, count(*) as Views FROM watchhistory GROUP BY titleid ORDER BY Views DESC limit 10;
  titleid   | views
------------+-------
 tt7631348  |  1307
 tt14627576 |  1065
 tt8372506  |  1063
 tt5793632  |  1056
 tt1403008  |  1053
 tt7825602  |  1051
 tt6840954  |  1046
 tt12780424 |  1042
 tt7266106  |  1036
 tt6539274  |  1035

The goal is to essentially replace this titleid column (from watchhistory) with the title (from media). I tried using joins between the watchhistory.titleid and media.mediaid with no luck.

What SQL query do I need to get this desired table?

Thanks in advance.

Answer 1

You need to INNER JOIN to your media table on mediaid:

SELECT m.title, count(wh.*) as Views 
FROM watchhistory wh
 INNER JOIN media m on m.mediaid = wh.titleid 
GROUP BY m.mediaid 
ORDER BY Views DESC LIMIT 10;

To see what the select and join are doing, you can simplify it:

SELECT m.*, wh.* 
FROM watchhistory wh
 INNER JOIN media m on m.mediaid = wh.titleid

The result will be a joined 'table' that has the two tables combined on the mediaid/titleid.