How to double inner join in mysql

Question

This is my table

If i select the date 20160730 (week 30), it will show all the dayID in week 30 at the dayRemark table.

In addition, I also want limit in middle 3 result. Example: if result is: 20160725, 20160727, 20160728, 20160730, 20160731. I want my final result is: 20160728, 20160730, 20160731 (because my first selected date is 20160730)

Below is my code

SELECT dayRemark.dayID, calander.week_name, 
FROM dayRemark 
INNER JOIN calander 
    ON dayRemark.dayID = calander.dayID 
where dayRemark.dayID = '20160730' 
group by dayRemark.dayId

How I put the calander.week and limit in middle 3?

Answer 1

You could use UNION and LIMIT to select the the N rows where dayID is greated than the requested date, and do the same for the N rows where dayID is smaller. In your case N=1 so you have:

(
SELECT dayRemark.dayID, calendar.week_name
FROM dayRemark 
INNER JOIN calendar ON dayRemark.dayID = calendar.dayID 
where dayRemark.dayID < '20160730' 
group by dayRemark.dayId
ORDER BY dayRemark.dayID DESC
LIMIT 1)
UNION (
SELECT dayRemark.dayID, calendar.week_name
FROM dayRemark 
INNER JOIN calendar ON dayRemark.dayID = calendar.dayID 
where dayRemark.dayID = '20160730' 
group by dayRemark.dayId
    )
UNION (
SELECT dayRemark.dayID, calendar.week_name
FROM dayRemark 
INNER JOIN calendar ON dayRemark.dayID = calendar.dayID 
where dayRemark.dayID > '20160730' 
group by dayRemark.dayId
ORDER BY dayRemark.dayID ASC
LIMIT 1
)

No sure it's the best way to do it.