0

I'm getting started with SQL and I've run against a problem. I have three tables I'm trying to include in a query. The "Items" table contains all of the items we have, "Sales Order Items" contains the transactional history of individual items, and "Sales Orders" contains the overall status of orders.

I'm trying to list every item in the "items" table and then show the quantity of these items from voided sales orders. I'm using the statement WHERE "Quantity" >= 0 OR "Quantity" IS NULL to ensure all items are listed even if the quantity is null (no transaction record). However, if I do this, I'm not sure how to also have the WHERE statement for Status = 'void'

This is the (awful) query as it stands now:

SELECT
"Items"."Item ID","Items"."Item Name",sum(if_null("Sales Order Items"."Quantity",0))
FROM
"Items"
LEFT JOIN "Sales Order Items" ON "Items"."Item Name" = "Sales Order Items"."Item Name"
LEFT JOIN "Sales Orders" ON "Sales Order Items"."Sales Order ID" = "Sales Orders"."Sales Order ID" 
WHERE "Sales Orders"."Status" = 'void' AND "Quantity" >= 0 OR "Quantity" IS NULL
GROUP BY "Items"."Item ID","Items"."Item Name";

This partially works, it lists items and their quantity on Sales Order that = 'void' Status, but, ≈20% of items don't appear.

The final goal is to show all items and their quantity on sales orders that != void to get true count of stock committed ("Sales Order Items"."Quantity" includes voided items), including null to run a simple formula on in a report later.

This is within Zoho Analytics query tables only allowing SELECT queries in any db dialect.

First 5 rows returned from above:

Item Name 'Void' Item Qty
ADP-BT-AU-2X1 0
ADP-DAI-AU-1X0-DEMO 0
ADP-DAO-AU-0X1 4
ADP-USB-AU-2X2 2
ADP-USB-AU-2X2-DEMO 0

This is the expected result but it is stripping out some null items from the list where it should return 0. First five rows should look like:

Item Name 'Void' Item Qty
ADP-AES3-AU-2X2 0
ADP-BT-AU-2X1 0
ADP-DAI-AU-1X0 0
ADP-DAI-AU-1X0-DEMO 0
ADP-DAI-AU-2X0 0
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
RTee
  • 21
  • 3
  • 1
    For an SQL question it normally makes sense to name the concrete `DBMS` that you are using, like for example `PostgreSQL` or `Db2` or whatever yours is. And your case also provide a few `Create Table ...` statements and SQL `Insert Into ...` sample data lines, say five or ten, and to tell or show the _expected_ result with the given test data, as opposed to the _observed_ result – Stefan Wuebbe Nov 04 '22 at 15:22
  • Try it with `"Sales Orders"."Status" = 'void' AND ("Quantity" >= 0 OR "Quantity" IS NULL)`. `AND` has higher precedence than `OR`, so you need to use parentheses to enforce precedence. – Mark Rotteveel Nov 04 '22 at 16:51

0 Answers0