I've acturally got my head around what you're saying.

My questions are, how can I retrieve a list of products or one specific product based on this?

Currently my code has a HAVING clause

Secondly what is the IN(1,2) referencing ?

Lately, why is there no FROM table? Might link to the first question


select productId, sum(qty) qty from (
 select productId , sum(quantity) qty from stock WHERE productId IN(1,2) group by productId
 union all
 select productId, -sum(quantity) qty from order_items WHERE productId IN(1,2) group by productId
) x group by productId

    1 and 2 are example productId values. the inner selects are selecting records with those product IDs.
    The inner SELECTs are selecting from the stock and order_items tables and the results are being combined (using union all) into a single relation that the outer SELECT is selecting rows from. (That concept of a relation is what a relational database is all about, which is why it's called that.)

    nzkiwi80 Thanks, so if I had a product 'Blue Jeans', it'd have that in my product table. I'd add a row to my stock table with a qnty of 10 for example.

    You should probably have one record in your stock table for each option e.g., "slim fit", possibly different sizes, etc.

    nzkiwi80 If I had no orders for that item, it'd show 10 available. If my order_items table had the product ID in it for 1 order it'd show 9? is that right?

    This would require that you always join the stock table to the orders table to get a count of your current inventory. I don't think that's a very good idea. As someone who sells various music-related merch, it has been my experience that there are many reasons to reduce your stock/inventory. E.g., I gave away a bunch of LPs for promotional reasons. Some merch gets broken or damaged. Etc. I might consider maintaining the current count of inventory by updating your stock table when you part with an item for any reason. You could decrement this value as each order is submitted. You might also occasionally count your inventory and simply update it.

        sneakyimp You should probably have one record in your stock table for each option e.g., "slim fit", possibly different sizes, etc.

        I expect each distinct item/variation would have its own SKU that uniquely identifies it; the products table would have a (single) distinct row for each SKU (and, as recognising the role of the SKU, another column in the table would be the number of units in stock).

          Weedpacket expect each distinct item/variation would have its own SKU that uniquely identifies it; the products table would have a (single) distinct row for each SKU (and, as recognising the role of the SKU, another column in the table would be the number of units in stock).

          If your products table has a lengthy description for some fancy pair of jeans, let's say, it would be a shame to repeat that lengthy description in all the different rows for each waist size / length combination for those jeans.

          Also do you mean to suggest that two different sizes of a particular pair of trousers would have distinct SKUs? It certainly makes sense to count the sizes separately -- you wouldn't want to continue selling a pant size you no longer had in stock.

            Weedpacket I've not seen a query like this, Where would I select or join to my product tables so I could query conditional of name or price etc?

              nzkiwi80

              You would do that in the outer/main query -

              select i.name, item_id, sum(qty) qty from (
 select item_id, sum(quantity) qty from stock WHERE item_id IN(1,2) group by item_id
 union all
 select item_id, -sum(quantity) qty from order_items WHERE item_id IN(1,2) group by item_id
) x
JOIN items i ON x.item_id = i.id
group by x.item_id

                  nzkiwi80 On the relations that have those properties (name and price would be properties of stock items, not order items (since items don't change name or unit price when sold)).

                      sneakyimp Also do you mean to suggest that two different sizes of a particular pair of trousers would have distinct SKUs?

                      That is the convention, yes. It might have a base component that is common to all variations, prefixed/suffixed if and where necessary to distinguish variants, there might be some clever encoding so that if you know your SKUs you can immediately see that "MMT56G" is a "Male Mannequin Torso, gloss finish", or it might just be a flat serial number; all that's up to whoever is keeping the stock since it's they who will be making and using the SKUs. But they want to be distinct so that, as you note, you don't claim to have size 32 trousers in stock only to discover you have nothing above size 28.

                          17 days later

                          Coming back to this theory. If I had produce ID 1 with one item in the stock table. When an order is made I add a -1 to the stock table and one to the order items rows.

                          if I was to check available levels I’d have -1

                          when I cancel the order I add one to the stock table I’m back at 0, if I don’t want to cancel my order items for reference I’m stuck unless I add another to the stock table.

                          why do I need to union the order items table? Can’t I just do all the math in the stock table?

                          nzkiwi80

                          You don't record the same piece of data in multiple places.

                          If someone places an order, it is only recorded in the order items table. For your example, product id 1, quantity 1. The UNION query SUMs the quantity in the stock table and subtracts the SUM of the quantity in the order items table. For your example, stock (1) - order items (1) = 0.

                          Canceling an order or canceling one or more item(s) in an order should involve setting a status value in the order items table that would exclude those rows from the UNION query, by adding a condition in the WHERE clause, e.g. status != 'canceled'. For your example, stock (1) - order items (0) = 1.

                          The suggestion to use a separate order items table and a stock table is because the rest of the data being recorded in each row in these tables is different. These are also two different contexts. A positive quantity ordered, is a reduction in stock and a positive quantity put into stock, increases available stock. By trying to use a single table, you will have unused columns and must deal with when is a quantity treated as positive and when is it treated as negative.

                              Write a Reply...