1

I'm trying to run a single query that will list results based on parent-child relationship as well as count number of records for each parent category. Here is my table called inbox:

id      name                parent_id       created
====================================================
1       First Message       0               2016-02-05
2       sub1 (1) message    1               2016-02-15
3       sub2 (2) message    1               2016-02-18
4       Second Message      0               2016-03-01
5       sub3 (1) message    4               2016-03-18
6       sub4 (2) message    4               2016-03-20
7       sub5 (3) message    4               2016-03-21

The desired result is to display each parent category (parent_id = 0), among with single latest entry from child category and include counter for each parent. Something like:

total (3) Second Message
   -- sub4 (2) message

total (2) First Message
   -- sub5 (3) message
Alko
  • 1,421
  • 5
  • 25
  • 51
  • 2
    http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 shows how to get the latest row in each group. If you add `COUNT(*)` to the subquery that has `GROUP BY`, you can also get the count. – Barmar Apr 05 '16 at 01:43
  • Your expected results does not work for your sample data, as you have the same `id` for the last 2 rows, and they both have `parent_id` of `4`, so they would be in the same grouping. – Sean Apr 05 '16 at 01:45
  • @Sean I have fixed they ID typo. – Alko Apr 05 '16 at 01:51
  • just being nit-picky, but what about the `parent_id`? Based off your sample data, I see the last entries should be `sub2 (2) message` for `First Message` and `sub5 (3) message` for the `Second Message`. So I don't get how you have `sub5 (3) message` and `sub4 (2) message`, as they both have `parent_id` of `4`. – Sean Apr 05 '16 at 01:54
  • The last 3 sub messages are children of Second message. Second message has ID 4 and therefore all the children are assigned the same parent_id, the same goes for the first 2 sub messages – Alko Apr 05 '16 at 02:06
  • We're not here to do your work for you. I've given you a pointer to a question that shows how to solve part of it, and explained the trivial addition you need to make to it. Please show what you've tried and ask for help if you can't get it working. – Barmar Apr 05 '16 at 02:07
  • @Barmar The url that you have provided does not relate to my question. Only partially, selecting one row, while my question is about relations between parent child as well as one row per parent – Alko Apr 05 '16 at 02:19
  • 1
    You get the parent name with a simple join after you do the aggregation by `parent_id`. – Barmar Apr 05 '16 at 02:21

0 Answers0