MySQL子查询与主查询值的日期时间比较

I am trying to do what seems like a simple query.. I have a query which works fine until I try to add a subquery to the select clause. I am trying to add a column by querying a second table with the dates I get from the first. I don't know if a join might be better. If you look at my first sample it returns every record in my second table instead of using the date range from the outer select statement.I am trying to do what seems like a simple quer