mysql query trying to search by alias involving CASES and aggregate functions UGH!
- by dqhendricks
I have two tables left joined. The query is grouped by the left table's ID column. The right table has a date column called close_date. The problem is, if there are any right table records that have not been closed (thus having a close_date of 0000-00-00), then I do not want any of the left table records to be shown, and if there are NO right table records with a close_date of 0000-00-00, I would like only the right table record with the MAX close date to be returned.
So for simplicity sake, let's say the tables look like this:
Table1
id
1
2
Table2
table1_id | close_date
1 | 0000-00-00
1 | 2010-01-01
2 | 2010-01-01
2 | 2010-01-02
I would like the query to only return this:
Table1.id | Table2.close_date
2 | 2010-01-02
I tried to come up with an answer using aliased CASES and aggregate functions, but I could not search by the result, and I was attempting not to make a 3 mile long query to solve the problem. I looked through a few of the related posts on here, but none seem to meet the criteria of this particular case. Any pushes in the right direction would be greatly appreciated. Thanks!