skip to Main Content

Some background: I am creating a POS system and have a MySQL Database. The design of my database is one table "tblProducts" is keeping track of product UPC, description, price, etc. The other table "tblStock" is keeping track of quantity, min and max. There is a FK connecting UPC’s between tblStock and tblProducts. When I scan a product into my program, it adds the UPC to tblStock.

Problem: I need to SELECT from my database all products that are:

  1. "Quantity" < "Max"
  2. "Ordered_Flag" = 0

I have this working, however the tricky part I need help with is that if 1) is true, I need all products that have the exact same description to also show, even if those products are not below the Max value and if those UPC’s are not even in tblStock

The reason for needing products with the same description is my workaround to suppliers having different codes for the same product

Let me know if you need a better explanation… I had a hard time trying to put this in words

Here is what I have so far

SELECT p.id, p.upc, p.description, p.purchasePrice, p.salePrice, p.supplier, p.supplierSku, p.moveCode, 
       s.quantity, s.min, s.max, s.orderedFlag 
FROM tblProducts p 
INNER JOIN tblStock s ON p.upc = s.upc 
WHERE s.orderedFlag = 0 AND s.quantity < s.max 
ORDER BY p.description, p.purchasePrice;

DBFIDDLE: https://www.db-fiddle.com/f/ctM5CK5myvor7PhFYPqEDq/6

When I run my query below, it excludes ID 5 because it is not in the tblStock

2

Answers


  1. It sounds to me like you need to index tblProducts.description and union a second select:

    SELECT p.id, p.upc, p.description, p.purchasePrice, p.salePrice, p.supplier, p.supplierSku, p.moveCode, s.quantity, s.min, s.max, s.orderedFlag 
    FROM tblStock s
    INNER JOIN tblProducts p ON p.upc = s.upc
    WHERE s.orderedFlag = 0 AND s.quantity < s.max
    
    UNION ALL
    
    SELECT p.id, p.upc, p.description, p.purchasePrice, p.salePrice, p.supplier, p.supplierSku, p.moveCode, s.quantity, s.min, s.max, s.orderedFlag 
    FROM tblStock s
    INNER JOIN tblProducts p ON p.description = s.description AND p.upc <> s.upc
    WHERE s.orderedFlag = 0 AND s.quantity < s.max
    
    ORDER BY description, purchasePrice;
    

    (I switched the order of your tables; it is usually more readable to make the only table you are filtering on be the FROM table.)

    You could just join on the description or the upc matching, but that likely will not perform as well.

    Login or Signup to reply.
  2. I used your query as a temporary table and joined it again with tblProducts on the description column.

    SELECT * FROM (
        SELECT p.id, p.upc, p.description, p.purchasePrice, p.salePrice, p.supplier, p.supplierSku, p.moveCode, 
           s.quantity, s.min, s.max, s.orderedFlag 
        FROM tblProducts p 
           INNER JOIN tblStock s ON p.upc = s.upc 
        WHERE s.orderedFlag = 0 AND s.quantity < s.max 
           ORDER BY p.description, p.purchasePrice
    ) AS tmp
        LEFT JOIN tblProducts AS b ON tmp.description = b.description
    

    https://www.db-fiddle.com/f/t8xKa2kZQGrXU5ocdMMUwX/0

    I’m not sure if that’s what you want.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search