is it possible?

[Destramic]

hey guys,

 

what im trying to do is bring back a result of either a increment or a price + an increment...in this query item_id 2 doesn't exist so i want to return the result of a increment.

 

if i change item_id 2 to 1 then i get the result of price + a increment as the item_id exists.  is it possible to get a result of just the increment without doing another query if this query returns no row count please?

 

increments and bids have no relation

 

think i may be asking a impossible/stupid question

 

thank you

SELECT
       CASE WHEN b.price IS NULL
       THEN (SELECT increment FROM bid_increments ORDER BY increment DESC LIMIT 1)
       ELSE (b.price + bi.increment)
       END AS `minimum_bid`                 
FROM bids b
JOIN bid_increments bi
WHERE b.item_id = 2 
AND b.price BETWEEN bi.price_from AND bi.price_to
ORDER BY b.created_timestamp DESC
LIMIT 1

[Barand]

Can we see some data so we know what we are dealing with?

[Barand]

For now, from your current query, I'll assume the data looks something like this

+------------------------------------------------+   +-----------------------------------+
| Table bids                                     |   | Table bid_incrementss             |
+--------+---------+-------+---------------------+   +------------+----------+-----------+
| bid_id | item_id | price | created_timestamp   |   | price_from | price_to | increment |
+--------+---------+-------+---------------------+   +------------+----------+-----------+
|      1 |       3 | 10.00 | 2015-08-29 12:18:56 |   |       0.00 |     9.99 |      2.00 |
|      2 |       2 | 20.00 | 2015-08-29 12:18:56 |   |      10.00 |    19.99 |      3.00 |
|      3 |       4 | 30.00 | 2015-08-29 12:18:56 |   |      20.00 |    29.99 |      4.00 |
+--------+---------+-------+---------------------+   |      30.00 |    99.99 |      5.00 |
                                                     +------------+----------+-----------+

using this query

SELECT
    CASE WHEN b.price IS NULL
       THEN (SELECT MAX(increment) FROM bid_increments)
       ELSE (b.price + bi.increment)
    END AS `minimum_bid`                 
FROM bid_increments bi
LEFT JOIN bids b 
ON b.price BETWEEN bi.price_from AND bi.price_to
 	AND b.item_id = 1 
ORDER BY item_id DESC
LIMIT 1

the results are

item_id = 1         item_id = 2         item_id = 3         item_id = 4
+-------------+     +-------------+     +-------------+     +-------------+
| minimum_bid |     | minimum_bid |     | minimum_bid |     | minimum_bid |
+-------------+     +-------------+     +-------------+     +-------------+
|        5.00 |     |       24.00 |     |       13.00 |     |       35.00 |
+-------------+     +-------------+     +-------------+     +-------------+

[Destramic]

just what i'm after

 

thanks your for effort barand much appreciated