CODEWITHSUNDEEP
mysqlmariadb
arvernester
arvernester

Reputation: 189

How to order data on Inner Join MySQL?

I have a case to create podcast playlist based on category sorts. For example, I want to join podcast data and category data based on the table below.

> SELECT * FROM category_sorts;

+----+-------------+------+
| id | category_id | sort |
+----+-------------+------+
|  1 |           2 |    1 |
|  2 |           1 |    2 |
|  3 |           1 |    3 |
|  4 |           2 |    4 |
+----+-------------+------+

Column category_id related to categories table.

> SELECT * FROM categories;

+----+--------------+-----------+
| id | title        | status    |
+----+--------------+-----------+
|  1 | Saga         | published |
|  2 | Asia Calling | published |
+----+--------------+-----------+

And here some sample data from podcasts table with a related category.

> SELECT * FROM podcasts;

+----+-------------+------------------------+---------------------+
| id | category_id | title                  | published_at        |
+----+-------------+------------------------+---------------------+
|  1 |           1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 |
|  3 |           2 | Berita Asia!           | 2018-05-01 22:52:15 |
|  4 |           2 | Asia Later!            | 2018-05-01 23:47:13 |
|  5 |           1 | Saga terbaru           | 2018-05-02 00:06:32 |
+----+-------------+------------------------+---------------------+

Using the provided query below, I got duplicate podcasts in the same category sorts.

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.sort
    ORDER BY cs.sort ASC, p.published_at DESC

Results:

+----+------------------------+---------------------+----------------+------+
| id | podcast_title          | published_at        | category_title | sort |
+----+------------------------+---------------------+----------------+------+
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    1 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    2 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    3 |
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    4 |
+----+------------------------+---------------------+----------------+------+

What I expect?

I want to order playlist based on sort column and latest podcasts indicated by published_at column. Order using sort column is OK, but I can't order it by published_at date from podcasts.

Expected results:

+----+------------------------+---------------------+----------------+------+
| id | podcast_title          | published_at        | category_title | sort |
+----+------------------------+---------------------+----------------+------+
|  4 | Asia Later!            | 2018-05-01 22:52:15 | Asia Calling   |    1 |
|  5 | Saga Terbaru           | 2018-05-01 22:40:47 | Saga           |    2 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    3 |
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    4 |
+----+------------------------+---------------------+----------------+------+

Can someone give me a clue how to do with it? Any link or source will be appreciated.

Here is example full table structure and data from SQL Fiddle if you want to play with it.

Thank you.

Upvotes: 0

Views: 54

Answers (1)

Jarek.D
Jarek.D

Reputation: 1294

If you don't need multiple categories see my comment under your post but if you want to allow multiple categories as the examples seem to imply try the sql below. The only change is it groups by category_id so you don't get duplicates:

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.category_id
    ORDER BY cs.sort ASC, p.published_at DESC

or actually to make it more bulletproof introduce aggregate min(cs.sort) as sorting_order so the order is always picked correctly and use in ORDER clause:

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        min(cs.sort) as sorting_order
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.category_id
    ORDER BY sorting_order ASC, p.published_at DESC

But if you want to keep the category duplicates and get rid of podcast duplicates then:

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY p.id
    ORDER BY cs.sort ASC, p.published_at DESC

Fiddle here: http://sqlfiddle.com/#!9/3672ce/22

Upvotes: 1

Related Questions