Efficient SQL statement

Question

On getting random associative array pairs from fields product_option_id:product_option_value_id like {"230":"23","228":"19"}, I have to get the minimum quantity from the respective rows in the above TableX.

Currently, I query the database through PHP based on each pair, and store their quantity. i.e.- If there are 3 pairs, I query the database 3 times based on each pair to get the 3 quantities. Then I loop through the quantities to get the least one.

If the random {product_option_id:product_option_value_id} pairs are {"230":"23","228":"19"}, can you make an efficient SQL statement that gets the minimum quantity.

I have tried

SELECT quantity FROM TableX 
WHERE product_option_id=variable_product_option_id 
  AND product_option_value_id=variable_product_option_value_id

I run this in a PHP for loop for all the pair values. I store the quanity, and get the least one through PHP

The following code is based on my personal situation: `SELECT quantity FROM " . DB_PREFIX ."product_option_value WHERE product_option_id=".$key." AND product_option_value_id=".$value." AND subtract=1"`. I run this in a PHP for loop for all the pair values. I store the quanity, and get the least one through PHP. — user3980196, Sep 07 '16 at 14:00
In future, use the edit link to include extra info on your question. Nobody can read code in a comment — RiggsFolly, Sep 07 '16 at 14:02
@kbball The basic info is the rows are determined through the pair values of product_option_id & product_option_value_id. Then we have to get the minimum quantity based on the rows. — user3980196, Sep 07 '16 at 14:10
You want the minimum quantity of the "pair"? Meaning the smaller value of the two? Or you want to sum the two? Are you looking for a minimum per row or for the table? Based on the data you have above, what is your expected result? — kjmerf, Sep 07 '16 at 14:16
hi @kbball. saw your comment just now. got the answer from Izo. thanks. — user3980196, Sep 07 '16 at 14:44
See the manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — Strawberry, Sep 07 '16 at 14:58

Zoltan · Accepted Answer · 2016-09-07T14:41:29.327

You may try a query like this one:

select product_option_id, product_option_value_id, min(quantity)
    from product_option_value
    where (product_option_id = 230 and product_option_value_id = 43)
        or (product_option_id = 228 and product_option_value_id = 49)
    group by product_option_id, product_option_value_id;

In practice:

> create table product_option_value(product_option_id int, product_option_value_id int, quantity int);

> insert into product_option_value values
>     (230, 43, 2),
>     (230, 32, 1),
>     (228, 49, 1),
>     (228, 50, 0),
>     (229, 50, 1),
>     (229, 49, 1),
>     (230, 43, 8),
>     (230, 32, 9),
>     (228, 49, 11),
>     (228, 50, 10),
>     (229, 50, 5),
>     (229, 49, 4);

> select product_option_id, product_option_value_id, min(quantity)
>     from product_option_value
>     where (product_option_id = 230 and product_option_value_id = 43)
>         or (product_option_id = 228 and product_option_value_id = 49)
>     group by product_option_id, product_option_value_id;
+-------------------+-------------------------+---------------+
| product_option_id | product_option_value_id | min(quantity) |
+-------------------+-------------------------+---------------+
| 228               | 49                      | 1             |
| 230               | 43                      | 2             |
+-------------------+-------------------------+---------------+

You should also quote the query parameters properly. Using constructs like ...product_option_id=".$key."... (excerpt from your example from the comments) is error-prone, as $key may contain anything, like Little Boby Tables.

Edit: To get just a single minimum for all such groups, you just drop the group by and the grouping columns, like this:

> select min(quantity)
>     from product_option_value
>     where (product_option_id = 230 and product_option_value_id = 43)
>         or (product_option_id = 228 and product_option_value_id = 49);
+---------------+
| min(quantity) |
+---------------+
| 1             |
+---------------+

Thanks Izo. Although your solution wasn't exactly I was looking for, I now know how to handle the issue. In your excellent example, the result is 2 rows with quantity of 1 & 2. I just wanted to get a single row with the minimum quantity i.e.- 1. — user3980196, Sep 07 '16 at 14:25
Ah, I see. In that case it's even easier. I updated my answer to cover this use-case as well. — Zoltan, Sep 07 '16 at 14:42

score 0 · Answer 2 · edited Sep 07 '16 at 14:03

0

I think you should try following one query to get your result.

To get this kind of result product_option_id:product_option_value_id you should use CONCAT function of MYSQL.

SELECT CONCAT(product_option_id,':',product_option_value_id) 
FROM TABLE 
WHERE subtract = 1 
GROUP BY product_option_id 
ORDER BY product_option_value_id ASC

Also you can put additional where conditions.

Please try this.

edited Sep 07 '16 at 14:03

RiggsFolly

93,638
21
103
149

answered Sep 07 '16 at 14:02

Hardik Patel

706
5
14

Efficient SQL statement

2 Answers2