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
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.
Ok, let's split this in parts:
First, select the max date from the parent table, grouping by month:
select max(`date`) as max_date from parent group by last_day(`date`)
-- The "last_day()" function returns the last day of the month for a given date
Then, select the corresponding row from the parent table:
select parent.*
from parent
inner join (select max(`date`) as max_date from parent group by last_day(`date`)) as a
on parent.`date` = a.max_date
Finally, select the corresponding rows in the child table:
select child.*
from child
inner join parent
on child.parent_id = parent.id
inner join (select max(`date`) as max_date from parent group by last_day(`date`)) as a
on parent.`date` = a.max_date;
You can check how this works on this SQL fiddle.
EDIT
The above solution works, but if your tables are big, you may face a problem because the joined data is not indexed. One way to solve this is to create a temporary table and use this temp table to get your final result:
drop table if exists temp_max_date;
create temporary table temp_max_date
select max(`date`) as max_date
from parent
group by last_day(`date`);
alter table temp_max_date
add index idx_max_date(max_date);
-- Get the final data:
select child.*
from child
inner join parent
on child.parent_id = parent.id
inner join temp_max_date as a
on parent.`date` = a.max_date;
Here's the SQL fiddle for this second solution.
Temporary tables are only accesible to the connection that creates them, and are destroyed when the connection is closed or killed.
Remember: Add the appropriate indexes to your tables.
Use this SQL:
SELECT * FROM Child WHERE PARENT_ID IN (
SELECT MAX(ID) FROM Parent GROUP BY LAST_DAY(DATE)
)
Here is the working SQL Fiddle: http://sqlfiddle.com/#!9/d8880/8
This solution is clunky in its inability to properly employ indexes, but it's easier to write than one that would...
SELECT a.*
, c.id
FROM parent a
JOIN
( SELECT DATE_FORMAT(date,'%Y-%m')yearmonth
, MAX(date) max_date
FROM parent
GROUP
BY yearmonth
) b
ON b.max_date = a.date
JOIN child c
ON c.parent_id = a.id;
