1

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

Ivan
  • 14,692
  • 17
  • 59
  • 96
  • A view it's not an option due to database is using MyISAM. As far as I know MyISAM doesn't support views :( – Ivan Dec 01 '10 at 14:58

3 Answers3

0

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
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

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.

Conan
  • 3,074
  • 1
  • 22
  • 24