0

I have the following problem:

Scenario:

I've recomposed the table structure to indicate the problem and to hide the irrelevant details.

Let's say I have a table of items.

Item: item_id | buyer_id | unit_id | status_id

All the columns have a not null value referencing to corresponding table.

Other table that I have is a price-catalog with the following structure:

Price: price_id | buyer_id | unit_id | status_id | price

Here any of the referencing values may have the value "-1" meaning "All". So we could have the following rows in price table:

| price_id | buyer_id | unit_id | status_id | price |
|        1 |       -1 |       1 |         2 |    15 |
|        2 |        1 |       1 |         2 |    25 |
|        3 |       -1 |      -1 |         3 |    13 |

Here price 1 would match items with any buyer_id and unit_id = 1, status_id = 2 and price 2 would math items with buyer_id = 1, unit_id = 1 and status_id = 2. Price 3 would match items with any buyer_id, any unit_id and status_id = 3.

If there are multiple prices matching an item the selection is done with the following logic: Take the price that has the most fields specified (smallest amount of -1 values). If there are multiple prices with the same amount of -1 values then we pick the one that has a value different from -1 in the buyer_id. If such doesn't exist we pick the one with "not -1" value at unit_id and last the one with "not -1" value at status.

We can assume that there are not multiple prices matching the exact same group of prices (all prices have a unique combination of buyer_id, unit_id and status_id.)

Problem:

Now I need to make query that selects the right price for each item in item-table. So far i got this:

SELECT item_id, price
FROM item
    INNER JOIN price ON 1=1
        AND (price.buyer_id = item.buyer_id OR price.buyer_id = -1)
        AND (price.unit_id = item.unit_id OR price.unit_id = -1)
        AND (price.status_id = item.status_id OR price.status_id = -1)

Basically this will get all the matching prices. So if we have item with buyer_id = 1, unit_id = 1 and status_id = 2 we would get two rows:

| item_id | price |
|       1 |    15 |
|       1 |    25 |

My first idea was to use GROUP BY, but I haven't figured out how to do it so that the price selected is the right one and not just a random/first(?) one.

EDIT: Tried to order the rows (ORDER BY buyer_id DESC, unit_id DESC, status_id DESC) and then group the results based on the ordered sub-query but it seems that the order by doesn't work that way.

So how can I select the right row in GROUP BY? Or what would be an alternative solution to get right prices with a single query (sub-queries are fine)?

Altering the table structure is not really an option in this particular case.

Update

I've been using the solution I submitted earlier but the amount of criteria that the price is selected by has gone up from three to five. This means that I currently have a list of 32 different combinations in my CASE WHEN structure. In case I would need to add another one the list will double again, so I'm still looking for a better solution.

Community
  • 1
  • 1
Gonnagle
  • 112
  • 1
  • 14

1 Answers1

0

For now the problem was solved as follows. It does the job and at least in the specific case perform fast enough, but I would hope a better solution exists. For now this is what does the trick.

Because I have quite a small group of possible combinations it is relatively easy to form a CASE clause to give numeric value for each combination to represent the order of the different combinations. Next we'll pick for each item_id the row that has the smallest selector value. This is done by Joining with simple group-identifier, max-value-in-group Sub-query. Here is a query to demonstrate the idea:

SELECT item_id, price
FROM (
    SELECT item_id, price,
        CASE
            WHEN price.buyer_id <> -1 AND price.unit_id <> -1 AND price.status_id <> -1 THEN 1
            WHEN price.buyer_id <> -1 AND price.unit_id <> -1 AND price.status_id =  -1 THEN 2
            WHEN price.buyer_id <> -1 AND price.unit_id =  -1 AND price.status_id <> -1 THEN 3
            WHEN price.buyer_id <> -1 AND price.unit_id =  -1 AND price.status_id =  -1 THEN 4
            WHEN price.buyer_id =  -1 AND price.unit_id <> -1 AND price.status_id <> -1 THEN 5
            WHEN price.buyer_id =  -1 AND price.unit_id <> -1 AND price.status_id =  -1 THEN 6
            WHEN price.buyer_id =  -1 AND price.unit_id =  -1 AND price.status_id <> -1 THEN 7
            WHEN price.buyer_id =  -1 AND price.unit_id =  -1 AND price.status_id =  -1 THEN 8
        END AS selector
    FROM item
        INNER JOIN price ON 1=1
            AND (price.buyer_id = item.buyer_id OR price.buyer_id = -1)
            AND (price.unit_id = item.unit_id OR price.unit_id = -1)
            AND (price.status_id = item.status_id OR price.status_id = -1) 
    ) AS all_possible_prices

    INNER JOIN (
    SELECT item_id, MIN(selector)
    FROM (
        SELECT item_id
            CASE
                WHEN price.buyer_id <> -1 AND price.unit_id <> -1 AND price.status_id <> -1 THEN 1
                WHEN price.buyer_id <> -1 AND price.unit_id <> -1 AND price.status_id =  -1 THEN 2
                WHEN price.buyer_id <> -1 AND price.unit_id =  -1 AND price.status_id <> -1 THEN 3
                WHEN price.buyer_id <> -1 AND price.unit_id =  -1 AND price.status_id =  -1 THEN 4
                WHEN price.buyer_id =  -1 AND price.unit_id <> -1 AND price.status_id <> -1 THEN 5
                WHEN price.buyer_id =  -1 AND price.unit_id <> -1 AND price.status_id =  -1 THEN 6
                WHEN price.buyer_id =  -1 AND price.unit_id =  -1 AND price.status_id <> -1 THEN 7
                WHEN price.buyer_id =  -1 AND price.unit_id =  -1 AND price.status_id =  -1 THEN 8
            END AS selector
        FROM item
            INNER JOIN price ON 1=1
                AND (price.buyer_id = item.buyer_id OR price.buyer_id = -1)
                AND (price.unit_id = item.unit_id OR price.unit_id = -1)
                AND (price.status_id = item.status_id OR price.status_id = -1) ) AS min_seeds
    GROUP BY item_id
    ) AS min_selectors ON all_possible_prices.item_id = min_selectors.item_id 
                            AND all_possible_prices.selector = min_selectors.selector
Community
  • 1
  • 1
Gonnagle
  • 112
  • 1
  • 14