SQL select query join

Question

Below are my MYSQL tables. I am not able to figure out what a MySQl query looks like that selects only one row from parent for each month (by latest date in a month) and its consequent child rows. So in the given example it should return rows from the child table with IDs 4,5,6,10,11,12

Answer 1

I think something like the following would do the trick for you:

SELECT Child.id 
FROM parent 
    INNER JOIN Child ON parent.id = child.parent_id
WHERE parent.`date` IN (SELECT max(`date`) FROM parent GROUP BY YEAR(`date`), MONTH(`date`))

The fun part is the WHERE clause where we only grab parent table records where the date is the max(date) for that particular month/year combination.