MYSQL joining multiple queries based on condition

Question

Table x
x-id
userid
startdate
enddate

Table Y
Y-id
userid
loginid
startdate
enddate

In Table x a user will have only one entry but in Table y the same user can have multiple entry.

select * from x-id where enddate BETWEEN DATE( CURDATE( ) ) AND DATE_ADD( CURDATE( ) , INTERVAL 7 DAY )

In this query i need to check with table x enddate between next 7 days if no entry in table y. But if exist a entry in table y i need to check for the enddate for the latest entry from table y instead of x How to do that?

Answer 1

SELECT  x.*
FROM    x
LEFT JOIN
        y
ON      y.userid = x.userid
GROUP BY
        x.id
HAVING  COALESCE(MAX(y.enddate), x.enddate) BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY