return only last inserted parent id on childs table of multiple duplicate parents ids

Question

I'm trying to get values of last inserted parents id on childs table then join the grandparents table to get the property and their totals.

Here is my table structure:

parents
+------------
|pid | item |
+----+------+
| 1  | ite1 |
| 2  | ite2 |
+-----------+

childs
+-------------+
| cid  | pid  |
+------+------+
| 1    | 1    | -- not
| 2    | 1    | ---- row to be selected(last inserted to join grandparents)
| 3    | 2    | -- not 
| 4    | 2    | ---- row to be selected(last inserted to join grandparents)
+-------------+

grandparents
+----------------------+
| gid | cid | property |
+-----+-----+----------+
| 1   | 1   | 1200     |
| 2   | 1   | 1500     |
| 3   | 2   | 101      |
| 4   | 2   | 303      |
| 5   | 3   | 600      |
| 6   | 3   | 10       |
| 7   | 4   | 335      |
| 8   | 4   | 250      |
+----------------------+

results

+----------------------------+
| item   | cid  |  property  |
+--------+------+------------+
| ite1   | 2    | 101        |
| ite1   | 2    | 303        |
| ite1   | 4    | 335        |
| ite1   | 4    | 250        |
+----------------------------+

Total property results : sum(101 + 303 +335 + 250) =   989

I tried this query but return/ includes the rows( with not as shown on childs ) which cause the total of whole grandparents table

query:

SELECT g.property from grandparents g
join childs c on g.cid = c.cid
join parents p on c.pid = p.pid
where c.pid in (select DISTINCT pid from childs) and c.pid = 1 

Answer 1

You can try to use a subquery to get MAX(cid) then do JOIN by subquery's cid

SELECT g.property 
from grandparents g
join (
    SELECT pid ,MAX(cid) cid
    FROM childs 
    GROUP BY pid  
) c on g.cid = c.cid
join parents p on c.pid = p.pid