Reuse expression in SQL sentence

I'm using 开发者_运维技巧MySQL and I'm going mad trying to simplify a bit complex SQL sentence.

The query is this:

SELECT `provider`.*,`products`.`placement`,`price`.`price`+
   IFNULL((SELECT `price` FROM `price` WHERE `handle`=
     (SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0') AS `price`
FROM `provider` 
LEFT JOIN `products` ON `provider`.`id`=`products`.`web` 
LEFT JOIN `price` ON `price`.`handle`=`provider`.`id` 
WHERE `products`.`type`='$PRODUCT_TYPE'
  AND `price`.`price`+
   IFNULL((SELECT `price` FROM `price` WHERE `handle`=
     (SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')>0

This query is working perfect, but the problem is that I have a repeated item and I don't know how to simplify it. The repeated item I'm talking is:

  `price`.`price`+
   IFNULL((SELECT `price` FROM `price` WHERE `handle`=
     (SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')

Any idea to simplify it? Thanks

Create a view of your data that includes the extra column and query this view. Once you have created the view the query becomes very simple:

SELECT *
FROM your_view
WHERE type = 42
AND price > 0

The SELECT is evaluated after the WHERE clause so column aliases defined in the SELECT are not available to use in the WHERE.

MySQL is unusual in that it allows you to use column aliases in the having clause though so you could possibly rework the query to move the predicate from the where clause to a having clause.

Alternatively you could create a view or derived table with the following definition

SELECT `provider`.*          ,
       `products`.`placement`,
       `price`.`price`+ IFNULL(
       (SELECT `price`
       FROM    `price`
       WHERE   `handle`=
               (SELECT `group`
               FROM    `group_provider`
               WHERE   `provider_id`=`provider`.`id`
               )
       )
       ,'0')>0 AS `price`
FROM   `provider`
       LEFT JOIN `products`
       ON     `provider`.`id`=`products`.`web`
       LEFT JOIN `price`
       ON     `price`.`handle`=`provider`.`id`

and reference the column aliases in the WHERE clause in a SELECT from that as per Mark's answer. I'm not sufficiently au fait with the MySQL query optimiser to know if either of these approaches would have performance implications.

Try this, it should work.

First, you should create view

    CREATE VIEW `DB`.`view1` AS 
    SELECT `provider`.*, `products`.`placement`, `price`.`price`+ IFNULL((SELECT
    `price` FROM `price` WHERE `handle`= (SELECT `group` FROM `group_provider`
    WHERE `provider_id`=`provider`.`id`)),'0') AS `price`
    FROM `provider`
    LEFT JOIN `products` ON `provider`.`id`=`products`.`web`
    LEFT JOIN `price` ON `price`.`handle`=`provider`.`id`

Create query select for that view

    SELECT * FROM `view1` WHERE `price` > 0

This only work, if you use InnoDB.