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
2
Answers
It sounds to me like you need to index tblProducts.description and union a second select:
(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.
I used your query as a
temporary
table and joined it again with tblProducts on thedescription
column.https://www.db-fiddle.com/f/t8xKa2kZQGrXU5ocdMMUwX/0
I’m not sure if that’s what you want.