CODEWITHSUNDEEP
mysql
Eugene
Eugene

Reputation: 13

How to combine left join and subtract?

First table "Positions" contains information about items, available amount and price.

When somebody wants to buy an item, the system finds a position for that item with lowest price and create a reserve for it for some period of time.

Positions:

id item amount price seller
1  1    1      4     1
2  1    2      5     2

Reserves:

id position created_at 
1  1        1430060037

If another customer wants to buy the same item, only second position will be available for him, because first position has only one item (in amount) and that item has been reserved earlier. But if reserve#1 has been expired, customer could buy an item for the price from first position.

Updated:

Positions:

id  item amount price
1   1    2      4
2   1    2      5
3   1    2      6

Reserves:

id  position  created_at
1   1         1430060037
1   1         1430060038

In this case, I want to get position#2 with price#5, because all items from position#1 are reserved.

Upvotes: 0

Views: 56

Answers (1)

elixenide
elixenide

Reputation: 44841

Your question is unclear, but I think you want something like this:

SELECT
    `positions`.`amount`,
    MIN(`price`) AS `min_price`,
    COUNT(`reserves`.`id`) AS `reserve_count`
FROM `positions`
    LEFT JOIN `reserves` on `positions`.`id` = `reserves`.`position`
WHERE `positions`.`item` = 1234 -- specify the item here
    AND `created_at` >= NOW() - INTERVAL 60 SECOND
GROUP BY `reserves`.`position`
HAVING `reserve_count` != `amount`

Upvotes: 1

Related Questions