I have 3 tables:
id (PK), sku, product_name, product_description, current_price, quantity_in_stock.
id (PK), invoice_number, customer_id, user_account_id, total_price, time_issued, time_due, time_paid, time_cancelled, time_refunded.
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
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:
- Product table: https://ibb.co/rys40sv
- Invoice table: https://ibb.co/NV68gkY
- 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
Advanced Night Repair-Estee Lauder is the product name.