I have 2 tables in MySQL: city & user

here is the user table

and the city table city | start_date | -----| ---------------| jkt | 2021-10-17 | jbi | 2021-10-19 |

I would like to select user & city with start date base on start_date at city table. this is an example result.

I already query select city.city, date, user from city inner join user on city.city=user.city but stils I have no idea how to create condition with start date due to each city have different start date.

CodePudding user response：

You need to use the start date of the city dynamically:

select city.city, `date`, `user`
from city
join `user`
on city.city = `user`.city and
   `user`.`date` >= city.start_date