mysql query trying to search by alias involving CASES and aggregate functions UGH!

Posted by dqhendricks on Stack Overflow See other posts from Stack Overflow or by dqhendricks
Published on 2010-12-23T17:52:09Z Indexed on 2010/12/23 17:54 UTC
Read the original article Hit count: 184

Filed under:
|

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!

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query