SQL Complex View

Question

I would like to create a view from MySQL with 4 tables :

Customer

customer_id	lastname
1	Doe
2	Smith

Order

order_id	date	customer_id
1	2021-09-15	1
2	2021-09-12	1

Product

product_id	ref	price
1	0001	15.00
2	0002	17.00
3	0003	13.50

product_to_order

product_id	order_id	quantity
1	1	2
2	1	1
1	2	3

As you can see, there's no order for customer #1 and no order for product #3.

I would like to create a full view for the whole datas in the base as :

lastname	order_date	product_ref	price	quantity
Doe	2021-09-15	0001	15.00	2
Doe	2021-09-15	0002	17.00	1
Doe	2021-09-12	0001	15.00	3
NULL	NULL	0003	13.50	NULL
Smith	NULL	NULL	NULL	NULL

So, i try to use some outer joins to get what i want :

SELECT c.lastname lastname, o.date order_date,
    p.ref product_ref, p.price price, po.quantity quantity
FROM customer c 
    LEFT JOIN òrder`o ON c.customer_id = o.customer_id
    NATURAL JOIN product_to_order po
    RIGHT JOIN product p ON po.product_id = p.product_id;

But got only products that was not in an order and not the customer.

Try to intersect or union but not successfully.

What i'm missing ?

Regards

My advice: Never ever use `NATURAL JOIN`. They are very danguerous in the sense that adding a column to a table at some time can make queries that have been running fine for years return wrong results, just because the name (ref, name, description, you name it) happens to match another table's column name. `NATURAL JOIN` should have never made it into the SQL standard in my opinion. — Thorsten Kettner, Sep 16 '21 at 13:36
Another advice: Never use `RIGHT OUTER JOIN`. They can make queries really hard to understand. Only use `LEFT OUTER JOIN` and `FULL OUTER JOIN` (the latter is not yet supported by MySQL, though) when building queries with outer joins. — Thorsten Kettner, Sep 16 '21 at 13:39
@Shadow: Do you really consider the link on how to emulate a full outer join in MySQL an appropriate answer here? With four tables involved? While full outer joins would be the correct way to do this, did MySQL support them, an emulation of full outer joins on four tables doesn't seem suitable to me. Jean-Luc Aubert already mentions that he tried a solution with `UNION` and this is a much better approach in my opinion. — Thorsten Kettner, Sep 16 '21 at 13:48

Thorsten Kettner · Answer 1 · 2021-09-16T13:40:26.913

You want to show all orders with their customer and their products. But you also want customers without orders and products without orders in your results. The easiest way to get there is via separate queries the results of which you glue together with UNION ALL.

SELECT c.lastname, o.order_date, p.ref as product_ref, p.price, po.quantity
FROM `order` o
JOIN customer c ON c.customer_id = o.customer_id
JOIN product_to_order po ON po.order_id = o.order_id
JOIN product p ON p.product_id = po.product_id

UNION ALL

SELECT lastname, null, null, null, null
FROM customer
WHERE customer_id NOT IN (select customer_id FROM `order`)

UNION ALL

SELECT null, null, p.ref, p.price, null
FROM product
WHERE product_id NOT IN (select product_id FROM product_to_order)

SQL Complex View

1 Answers1