How to replace "no data" by zeros

[Chris-H]

Hello,

I am using Libreoffice base 6.3.5.2 (x64) on Windows 10 with embedded database and have setup a small base with the idea to create invoices with a customer table and items table etc…
So here is one where I can’t find a solution by myself (newbie in learning process)
I have 3 table to define a current stock situation:

• ITEMS
• Items_In which is stock as it comes in
• SOLD which are the ones that are being invoiced and therefore get out from stock

I have found a simple way to deduct the sold items from the items in (in stock), the problem is that it will only display the items which have show data in SOLD. I would like to display the items which had no sales as current stock.
To better explain, we have say 10 items with stock and 2 sold, the below SQL will only show data of the 2 Items sold.

Assuming that this is due to the missing data (or NULL) on SOLD table, I have been trying various options using COALESCE and I cannot get to show all Items which have stock, still only the ones which show sales.
To be clear I would like to have a query which show all stock from all items, even if that is zero

My SQL is:
SELECT “ITEMS”.“SKU”, COALESCE ( “Items_IN”.“QTY”, 0 ) - COALESCE ( “SOLD”.“QTY”, 0 ) AS “STOCK_CURRENT” FROM “SOLD”, “ITEMS”, “Items_IN” WHERE “SOLD”.“ITEM_ID” = “ITEMS”.“ITEM_ID” AND “ITEMS”.“ITEM_ID” = “Items_IN”.“ITEM_ID”

If somebody has an idea or direction to follow as it seems that I am missing something in the procedure.

Thank you for your time.

[Ratslinger]

Hello,

Contrary to my initial reaction here ( Not getting correct results in query ) regarding the use of COALESCE, this is part of what is needed. You also, as mentioned in the answer, need totals first.

So get totals for Items_IN (by item id) and get totals for SOLD (by item id) and join to the ITEMS records. Then you can get list of all.

Sample is attached with some minimal table definitions. It is the only Query.

Sample ---- Test_Stock.odb

[Ratslinger]

Also be aware that LO Base does have both HSQLDB AND Firebird as embedded databases. Please be specific as to what you are using. SQL capabilities are different between the two.

[Chris-H]

Dear @ratslinger,
Many thanks for your answer, you are such such a gift to us all. It makes the LO experience a much better one. Well noted on the Database, will be more specific in the future. Again thanks for the hint, will study this now.

[Chris-H]

Dear @Ratslinger, one more question in these regards. While your SQL is a brillant work of arts, I have tryed to take it apart and represent it in design mode which was impossible. Reason I want to do this is that I may want to add or deduct another Table for items returned or credited. In your SQL you create “A” ON “ITEMS”.“ITEM_ID” and “B” ON “ITEMS”.“ITEM_ID”, are these meant to be fields or tables?
Or could I split this in sub queries?

[Ratslinger]

@Chris-H,

Please tone it down some. Thank you is sufficient as there are many others answering questions here who are smarter and more knowledgeable than I am.

Now the A and B are alias names assign to select statement results. A temporary table name. The ON "ITEMS"."ITEM_ID" = "A"."ITEM_ID" is what actually joins (connects, relates) this selection to the original select statement. Each join needs something to join it together. See → SQL Joins.

Also, this can be done in design mode. I recommend to stick with SQL mode as design mode is limited and it will help when needing to create SQL in possibly other venues. The LO documentation may be of some help → LibreOffice Base Handbook

[Chris-H]

Thank you so much @ratslinger for your answer. Will tone it down ( can’t change my nature either ). As per your suggestion, I will study the Handbook along with the rest of the material that you have provided.