MySQL where condition based on select query

Question

I am having the following query to fetch the data from the table.

SELECT rental_plans.*, 
       ( ( inventory.total_inventory 
           + vehicles.tmp_qty ) - Ifnull(reservation.total_reserved, 0) ) AS 
       vehicle_inventory 
FROM   `rental_plans` 
       INNER JOIN `vehicles` 
               ON `vehicles`.`id` = `rental_plans`.`vehicle_id` 
       LEFT JOIN (SELECT Count(*) AS total_inventory, 
                         vehicle_id 
                  FROM   vehicle_inventories 
                  GROUP  BY vehicle_id) AS inventory 
              ON `inventory`.`vehicle_id` = `vehicles`.`id` 
       LEFT JOIN (SELECT vehicle_id, 
                         Sum(qty) AS total_reserved 
                  FROM   `reservations` 
                  WHERE  ( '2018-12-18' BETWEEN pickup_date AND drop_date ) 
                          OR ( '2018-12-28' BETWEEN pickup_date AND drop_date ) 
                             AND `status` NOT IN ( 'RETURNED' ) 
                  GROUP  BY `vehicle_id`) AS `reservation` 
              ON `rental_plans`.`vehicle_id` = `reservation`.`vehicle_id` 
WHERE  `rental_plans`.`id` > 0 
       AND `rental_plans`.`pickup` = '1' 
       AND `rental_plans`.`drop` = '10' 
ORDER  BY `rental_plans`.`price` ASC

But I want to handle where condition based on vehicle_inventory.

I tried with ... AND vehicle_inventory > 16 order by `rental_plans`.`price` ASC but this generates the error that column not found in the table

Since that's a calculated column, and you do not have a group by on the overall query, you would put the formula for that column in the where clause. — Sloan Thrasher, Dec 19 '18 at 07:03
On a sidenote: Be aware that `AND status NOT IN ('RETURNED')` binds to the second `OR` only, as `AND` has precedence over `OR`. Use additional parentheses to change that if you want it different. Or better: use additional parentheses anyway to make your Intention clear. — Thorsten Kettner, Dec 19 '18 at 07:07
Another sidenote: as `inventory` is outer joined, you should use another `IFNULL` here: `( IFNULL(inventory.total_inventory, 0) + vehicles.tmp_qty )`. — Thorsten Kettner, Dec 19 '18 at 07:10

score 2 · Accepted Answer · answered Dec 19 '18 at 07:03

The reason is that vehicle_inventory is an alias and it is resolved after WHERE clause. You can read about that here. You can do something like:

SELECT * 
FROM   (SELECT rental_plans.*, 
               ( ( inventory.total_inventory 
                   + vehicles.tmp_qty ) - Ifnull(reservation.total_reserved, 0) 
               ) AS 
               vehicle_inventory 
        FROM   `rental_plans` 
               INNER JOIN `vehicles` 
                       ON `vehicles`.`id` = `rental_plans`.`vehicle_id` 
               LEFT JOIN (SELECT Count(*) AS total_inventory, 
                                 vehicle_id 
                          FROM   vehicle_inventories 
                          GROUP  BY vehicle_id) AS inventory 
                      ON `inventory`.`vehicle_id` = `vehicles`.`id` 
               LEFT JOIN (SELECT vehicle_id, 
                                 Sum(qty) AS total_reserved 
                          FROM   `reservations` 
                          WHERE  ( '2018-12-18' BETWEEN 
                                   pickup_date AND drop_date ) 
                                  OR ( '2018-12-28' BETWEEN 
                                       pickup_date AND drop_date ) 
                                     AND `status` NOT IN ( 'RETURNED' ) 
                          GROUP  BY `vehicle_id`) AS `reservation` 
                      ON `rental_plans`.`vehicle_id` = 
                         `reservation`.`vehicle_id` 
        WHERE  `rental_plans`.`id` > 0 
               AND `rental_plans`.`pickup` = '1' 
               AND `rental_plans`.`drop` = '10')a 
WHERE  a.rental_plans > 16 
ORDER  BY `price` ASC

or

SELECT     rental_plans.*, 
           ( (inventory.total_inventory + vehicles.tmp_qty) - Ifnull(reservation.total_reserved, 0) ) AS vehicle_inventory
FROM       `rental_plans` 
INNER JOIN `vehicles` 
ON         `vehicles`.`id` = `rental_plans`.`vehicle_id` 
LEFT JOIN 
           ( 
                    SELECT   Count(*) AS total_inventory, 
                             vehicle_id 
                    FROM     vehicle_inventories 
                    GROUP BY vehicle_id) AS inventory 
ON         `inventory`.`vehicle_id` = `vehicles`.`id` 
LEFT JOIN 
           ( 
                    SELECT   vehicle_id, 
                             Sum(qty) AS total_reserved 
                    FROM     `reservations` 
                    WHERE    ( 
                                      '2018-12-18' BETWEEN pickup_date AND      drop_date) 
                    OR       ( 
                                      '2018-12-28' BETWEEN pickup_date AND      drop_date) 
                    AND      `status` NOT IN ('RETURNED') 
                    GROUP BY `vehicle_id`) AS `reservation` 
ON         `rental_plans`.`vehicle_id` = `reservation`.`vehicle_id` 
WHERE      `rental_plans`.`id` > 0 
AND        `rental_plans`.`pickup` = '1' 
AND        `rental_plans`.`drop` = '10' 
where      ( 
                      inventory.total_inventory + vehicles.tmp_qty) - ifnull(reservation.total_reserved, 0) > 16
ORDER BY   `rental_plans`.`price` ASC

Thank you for attention, I forgot to use the whole formula instead of a clause. — jogesh_pi, Dec 19 '18 at 07:11

MySQL where condition based on select query

1 Answers1