如何在SQL查询中使用ELSE？

Question

I have an SQL query to load items from my database.

$sql_query = "SELECT * FROM pagina_info 
              WHERE kwali='Luxuary' AND 
                    id IN (
                           SELECT CASE WHEN kwali='Luxuary' THEN min(id) 
                                       ELSE max(id) 
                           END 
                           FROM pagina_info 
                           GROUP BY product_name
                          ) 
               AND voorraad>='1' 
               LIMIT ".$getal1.", ".$getal2."";

Now my question, is there a word that i can use in my ELSE state for taking the next value?

in my select i have THEN min(id) ELSE max(id), instead of max(id) i need a word or something for next value.

I can't find anything usefull hopefully anyone here can help me.

Bram

Answer 1

There is no key word to get the next id. But I am unsure if you mean the next id for that series of records, or the next id that will be assigned as an auto increment primary key.

Assuming the first you could use a user variable to add a sequence number, or you could join the table against itself where the 2nd copy has a larger id but matches on the product name.

Depending on your exact requirements (some table layouts and test data would help) then something like one of these would work.

Firstly, sub query being used with an IN clause

SELECT * 
FROM pagina_info 
WHERE kwali='Luxuary'
AND id IN 
(   
    SELECT CASE 
                WHEN kwali='Luxuary' THEN min(a.id) 
                ELSE min(b.id) 
            END 
    FROM pagina_info a
    LEFT OUTER JOIN pagina_info b
    ON a.product_name = b.product_name
    AND a.id < b.id
    GROUP BY a.product_name
) 
AND voorraad>='1' 
LIMIT ".$getal1.", ".$getal2."";    

Or doing a join against a sub query.

SELECT pagina_info.* 
FROM pagina_info 
INNER JOIN 
(
    SELECT CASE 
                WHEN kwali='Luxuary' THEN min(a.id) 
                ELSE min(b.id) 
            END AS id
    FROM pagina_info a
    LEFT OUTER JOIN pagina_info b
    ON a.product_name = b.product_name
    AND a.id < b.id
    GROUP BY a.product_name
) c
ON pagina_info.id = c.id
WHERE pagina_info.kwali='Luxuary'
AND pagina_info.voorraad>='1' 
LIMIT ".$getal1.", ".$getal2."";