Query to Return each product name with its amount due, paid, cancelled, and refunded. Sql in 3 tables

I have 3 tables:

PRODUCT

Columns:

id (PK), sku, product_name, product_description, current_price, quantity_in_stock.

INVOICE

Columns:

id (PK), invoice_number, customer_id, user_account_id, total_price, time_issued, time_due, time_paid, time_cancelled, time_refunded.

INVOICE_ITEM

Columns:

id (PK), invoice_id (FK), product_id (PK), quantity, price, line_total_price.

I need to return each product name with its amount due, paid, cancelled, and refunded. Also order the result by product_name ascending.

I found this question to be very confusing since the tables are very complex. Can someone give me some intuition about this.

Examples of the database can be seen in the below images:

  1. Product table: https://ibb.co/rys40sv
  2. Invoice table: https://ibb.co/NV68gkY
  3. Invoice_item table: https://ibb.co/FnyFkqg

The first line of the result should be for example:

Advanced Night Repair-Estee Lauder 950.00 0.00 0.00 0.00

where Advanced Night Repair-Estee Lauder is the product name.



Read more here: https://stackoverflow.com/questions/64410604/query-to-return-each-product-name-with-its-amount-due-paid-cancelled-and-refu

Content Attribution

This content was originally published by Shivam Sharma at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: