Magento 1.9 – Select All Products with Special Price

You could join the query to the decimal table twice, picking up price once and special_price the second time, then use an if statement in the SELECT part of the query to return the right one. The answer you get may still be wrong though, as there's another two fields, special_to_date and special_from_date that you'll have to check to make sure the special price is valid.

The "proper" (more accurate but slower) way of doing your query there would be to use a ProductCollection to query for what you want at let Magento worry about how it's data structures work.

Magento completely ignores prices of associated products in the configurable product type. You can specify a price difference for each associated product within a configurable product, but the base price and special prices are always taken from the configurable product itself.

Conclusion is: You cannot achieve what you try to do without custom extensions. There are some extensions around that change how pricing for configurable products work, but due to these substantial changes there are always rewrites of important core classes and you should think twice if you really need it.

This extension is a good example: Better Configurable Products

But you should not expect it to work out of the box, at least not if you use a custom theme. This is not a "one click install and forget" extension, it should be installed and configured by a developer.