Question

List Customer and Product Without Sale

In one list return, all customers who do not have an invoice and all products that were not sold. Use the UNION operator in this query. Return 3 values:
- category - Is this line related to "customer" or "product"? Print "customer" or "product" without quotes.

- id - customer.id (category="customer") or product.id (category="product")

- name - customer.customer_name (category="customer") or product.product_name (category="product")

Table definitions and a data sample are given below.

Schema

Table: customer

column name	column type	key / NULL
id	int	PK
customer_name	varchar(255)
city_id	int	FK
customer_address	varchar(255)
contact_person	varchar(255)	N
email	varchar(128)
phone	varchar(128)

Table: product

column name	column type	key / NULL
id	int	PK
sku	varchar(32)
product_name	varchar(128)
product_description	text
current_price	decimal(8,2)
quantity_in_stock	int

Table: invoice

column name	column type	key / NULL
id	int	PK
invoice_number	varchar(255)
customer_id	int	FK
user_account_id	int
total_price	decimal(8,2)
time_issued	varchar	N
time_due	varchar	N
time_paid	varchar	N
time_canceled	varchar	N
time_refunded	varchar	N

invoice.customer_id references customer.id

Table: invoice_item

column name	column type	key / NULL
id	int	PK
invoice_id	int	FK
product_id	int	FK
quantity	int
price	decimal(8,2)
line_total_price	decimal(8,2)

invoice_item.invoice_id references invoice.id

invoice_item.product_id references product.id

Sample Data Tables

Table: customer

id	customer_name	city_id	customer_address	contact_person	email	phone
1	Drogerie Wien	1	Deckergasse 15A	Emil Steinbach	[email protected]	094234234
2	Cosmetics Store	4	Watling Street 347	Jeremy Corbyn	[email protected]	093923923
3	Kosmetikstudio	3	Rothenbaumchaussee 53	Willy Brandt	[email protected]	0941562222
4	Neue Kosmetik	1	Karlsplatz 2	NULL	[email protected]	094109253
5	Bio Kosmetik	2	Motzstraße 23	Clara Zetkin	[email protected]	093825825
6	K-Wien	1	Kärntner Straße 204	Maria Rauch-Kallat	[email protected]	093427002
7	Natural Cosmetics	4	Clerkenwell Road 14B	Glenda Jackson	[email protected]	093555123
8	Kosmetik Plus	2	Unter den Linden 1	Angela Merkel	[email protected]	094727727
9	New Line Cosmetics	4	Devonshire Street 92	Oliver Cromwell	[email protected]	093202404

Table: product

id	sku	product_name	product_description	current_price	quantity_in_stock
1	330120	Game Of Thrones - URBAN DECAY	Game Of Thrones Eyeshadow Palette	65	122
2	330121	Advanced Night Repair - ESTEE LAUDER	Advanced Night Repair Synchronized Recovery Complex II	98	51
3	330122	Rose Deep Hydration - FRESH	Rose Deep Hydration Facial Toner	45	34
4	330123	Pore-Perfecting Moisturizer - TATCHA	Pore-Perfecting Moisturizer & Cleanser Duo	25	393
5	330124	Capture Youth - DIOR	Capture Youth Serum Collection	95	74
6	330125	Slice of Glow - GLOW RECIPE	Slice of Glow Set	45	40
7	330126	Healthy Skin - KIEHL S SINCE 1851	Healthy Skin Squad	68	154
8	330127	Power Pair! - IT COSMETICS	IT is Your Skincare Power Pair! Best-Selling Moisturizer & Eye Cream Duo	80	0
9	330128	Dewy Skin Mist - TATCHA	Limited Edition Dewy Skin Mist Mini	20	281
10	330129	Silk Pillowcase - SLIP	Silk Pillowcase Duo + Scrunchies Kit	170	0

Table: invoice

id	invoice_number	customer_id	user_account_id	total_price	time_issued	time_due	time_paid	time_canceled	time_refunded
1	in_25181b07ba800c8d2fc967fe991807d9	7	4	1436	7/20/2019 3:05:07 PM	7/27/2019 3:05:07 PM	7/25/2019 9:24:12 AM	NULL	NULL
2	8fba0000fd456b27502b9f81e9d52481	9	2	1000	7/20/2019 3:07:11 PM	7/27/2019 3:07:11 PM	7/20/2019 3:10:32 PM	NULL	NULL
3	3b6638118246b6bcfd3dfcd9be487599	3	2	360	7/20/2019 3:06:15 PM	7/27/2019 3:06:15 PM	7/31/2019 9:22:11 PM	NULL	NULL
4	dfe7f0a01a682196cac0120a9adbb550	5	2	1675	7/20/2019 3:06:34 PM	7/27/2019 3:06:34 PM	NULL	NULL	NULL
5	2a24cc2ad4440d698878a0a1a71f70fa	6	2	9500	7/20/2019 3:06:42 PM	7/27/2019 3:06:42 PM	NULL	7/22/2019 11:17:02 AM	NULL
6	cbd304872ca6257716bcab8fc43204d7	4	2	150	7/20/2019 3:08:15 PM	7/27/2019 3:08:15 PM	7/27/2019 1:42:45 PM	NULL	7/27/2019 2:11:20 PM

Table: invoice_item

id	invoice_id	product_id	quantity	price	line_total_price
1	1	1	20	65	1300
2	1	7	2	68	136
3	1	5	10	100	1000
4	3	10	2	180	360
5	4	1	5	65	325
6	4	2	10	95	950
7	4	5	4	100	400
8	5	10	100	95	9500
9	6	4	6	25	150

The first 2 lines of the result should be:

category ("customer" or "product") id (customer.id or product.id) name (customer_name or product_name)

-> customer 2 Cosmetics Store
-> product 9 Dewy Skin Mist - TATCHA

Answer 1

This type of database queries requires combination of two select statements with a negation operator and a union of funtions.

Query to retrieve data is the given format is:

SELECT category,id,customer_name from customer WHERE id NOT IN(SELECT customer_id FROM invoice) UNION SELECT category,id,product_name FROM product WHERE id NOT IN(SELECT product_id FROM invoice_item);