MySQL - Select Statement-CodePudding

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:

"Quantity" < "Max"
"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

CodePudding user response：

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.

CodePudding user response：

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.