List Customer And Product Without Sale

Need to show the following columns (3) using UNION to return:

• All customers who do not have an invoice
• All products that were not sold

1. Category: Is this related to “customer” or “product”? Print “customer or “product”
2. ID: Customer.id (category=”customer”) or product.id (category=”product”)
3. Name: customer.customer_name (category=”customer”) or product.product_name (category=”product”)

Tables:

Customer

• id
• customer_name
• city_id
• customer_address
• contact_person
• email
• phone

Product

• id
• sku
• product_name
• product_description
• current_price
• quantity_in_stock

Invoice

• id
• invoice_number
• customer_id
• user_account_id
• total_price
• time_issued
• time_due
• time_paid
• time_canceled
• time_refunded

Invoice_Item

• id
• invoice_id
• product_id
• quantity
• price
• line_total_price

So far have the following:

SELECT 
  category,
  CASE
      WHEN category = 'customer' THEN c.id
      WHEN category = 'product' THEN p.id
  END AS 'id',
  CASE
    WHEN category = 'customer' THEN c.customer_name
    WHEN category = 'product' THEN p.product_name
  END AS 'name'
FROM 
  (
    SELECT
        CASE
          WHEN c.id = c.id THEN 'customer'
          WHEN p.id = p.id THEN 'product'
        END as 'category'
    FROM
        customer as c
    LEFT Join -- Left join to show all customers even those with & without invoices
        invoice as i
    ON c.id = i.customer_id
    AND i.id IS NULL -- Gives me all customers who do not have an invoice
    JOIN invoice_item as ii
    ON i.id = ii.invoice_id
    Join product p
    ON p.id = ii.product_id
  ) tb1

UNION ALL

SELECT 
  category,
  CASE
      WHEN category = 'customer' THEN c.id
      WHEN category = 'product' THEN p.id
  END AS 'id',
  CASE
    WHEN category = 'customer' THEN c.customer_name
    WHEN category = 'product' THEN p.product_name
  END AS 'name'
FROM
  (
    SELECT 
      CASE
        WHEN c.id = c.id THEN 'customer'
        WHEN p.id = p.id THEN 'product'
      END as 'category'
    FROM
        product as p
    LEFT JOIN -- Left join to show all products even those that sold and not sold
        invoice_item as ii
    ON p.id = ii.product_id
    AND ii.invoice_id IS NULL -- Gives me products that didnt sell
    JOIN invoice as i
    ON ii.invoice_id = i.id
  ) tb2

Open to any suggestions as I’m stuck trying to figure out how to show the category as either “product” or “customer”. Thanks in advance!

Answer

Considering your data model and requirements, you should try the below SQLs. You can easily perform UNION with both the SQLs.

First SQL returns this list –> All customers who do not have an invoice

select 'customer' as category, c.id as id, customer_name as name
     from customer c
     left join invoice i on c.id = i.customer_id
     where i.id is null 

Second SQL returns this list –> All products that were not sold

select 'product' as category, p.id as id, product_name as name
        from product p
        left join invoice_item ii on p.id = ii.product_id
        where ii.id is null;