CODEWITHSUNDEEP
mysql
Warren
Warren

Reputation: 163

MYSQL query: Ordering INNER JOIN data

I have 2 tables and my aim is to have one single SELECT query which returns the item name, how many sub items are related to that item and what the most recently added sub item is.

table_a

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| item_ID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_name | varchar(300) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

table_b

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| subitem_ID       | int(12)      | NO   | PRI | NULL    | auto_increment |
| subitem_relation | int(12)      | NO   |     | NULL    |                |
| subitem_name     | varchar(300) | NO   |     | NULL    |                |
| subitem_date     | datetime     | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

This is my data in table_b:

+------------+------------------+--------------+---------------------+
| subitem_ID | subitem_relation | subitem_name | subitem_date        |
+------------+------------------+--------------+---------------------+
|          1 |                1 | subitem a    | 2014-06-09 10:51:20 |
|          2 |                1 | subitem b    | 2014-06-10 05:21:52 |
|          3 |                2 | subitem c    | 2014-06-16 05:12:10 |
|          4 |                1 | subitem d    | 2014-06-18 06:42:14 |
|          5 |                2 | subitem e    | 2014-06-23 19:33:08 |
|          6 |                2 | subitem f    | 2014-07-02 12:30:25 |
|          7 |                2 | subitem g    | 2014-07-04 21:52:40 |
+------------+------------------+--------------+---------------------+

This is the query I currently have:

SELECT 
    a.item_name,
    COUNT(b.subitem_ID) as subitem_no,
    b.subitem_name as most_recent_subitem
FROM
    table_a as a
INNER JOIN 
    table_b as b
ON 
    a.item_ID = b.subitem_relation
GROUP BY 
    b.subitem_relation;

and this is the result:

+-------------+------------+---------------------+
| item_name   | subitem_no | most_recent_subitem |
+-------------+------------+---------------------+
| first item  |          3 | subitem a           |
| second item |          4 | subitem c           |
+-------------+------------+---------------------+

This returns the correct number of sub items however it returns the first related sub item rather than the most recent. I need to somehow sort the data in table_b to ORDER BY subitem_date DESC but not sure how to implement this.

The result I am looking for is:

+-------------+------------+---------------------+
| item_name   | subitem_no | most_recent_subitem |
+-------------+------------+---------------------+
| first item  |          3 | subitem d           |
| second item |          4 | subitem g           |
+-------------+------------+---------------------+

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would do this with the group_concat()/substring_index() trick:

SELECT a.item_name, COUNT(b.subitem_ID) as subitem_no,
       substring_index(group_cooncat(b.subitem_name order by subitem_date desc
                                    ), ',', 1) as most_recent_subitem
FROM table_a a INNER JOIN 
     table_b b
     ON  a.item_ID = b.subitem_relation
GROUP BY a.item_ID;

Upvotes: 2

Related Questions