The complex Query

Day after day I am surrounded in queries. Whether it be writing them, analyzing them, or just sorting through them as I look at code, I am introduced to queries normally every hour. This day was no different. Today, I had to write a query that took a lot of thought. I had to write a query that would find the minimum price (between a sale price and an original price), determine whether or not the product was in stock (or any of its subproducts were in stock), and sort it by 6 possible ways which was to be determined dynamically.

The query I ended up producing was:

  $sql['products']   = "select DISTINCT(A.name), A.id_product, A.thumbnail, IF (D.price <> 0, D.price, " .
                       "IF (MIN(C.price) <> 0, MIN(C.price), A.price)) as `price`, A.subproducts, " .
                       "IF (MIN(C.stock) <> 0, IF (MIN(C.stock) < 0, 1000, C.stock), IF (A.stock = -1, " .
                       "1000, A.stock)) as `stock`, A.description from `{$config['prefix']}Products` as " .
                       "A left join `{$config['prefix']}LinkedProducts` as B on (A.id_product=B.id_product) " .
                       "left join `{$config['prefix']}SubProducts` as C on (A.id_product=C.id_product) " .
                       "left join `{$config['prefix']}Sales` as D on (A.id_product=D.id_product and " .
                       "C.id_subproduct=D.id_subproduct and D.start >= NOW() and D.last < = NOW()) " .
                       "where A.id_category='{$_GET['id_category']}' or B.id_category='{$_GET['id_category']}' " .
                       "GROUP BY A.name order by `{$orderby}` {$direction} limit {$start}, " .
                       "{$config['pageProducts']}";

As you can tell, this is no ordinary query. It is a complex one that makes use of several joins and even if functions. An amatuer in this situation would be likely to split up that single query into 3-4 separate queries and several if statements in PHP. How many of you would have done it in a single query?