Getting wrong results on mysql queries

Question

I made a database for an e-commerce mockup.

Every looks fine so far, but when I do some Queries I get wrong results

I did a sqlfiddle for this so you can look into it

When I do

SELECT produkt_name, shipping_date 
FROM product, shipping 
WHERE shipping_date BETWEEN '2019-01-01' AND '2021-12-31'

I expect

produkt_name	shipping_date
Ballkleid elegant	2019-10-22
Cologne Tester Sammlung 1 (10 Flaschen)	2020-07-11
Glasbläserei Tier: Hase	2021-12-07

But I get this, which is basically every possible product with all existing shipping dates that exist in the database. Including products that were not even sold until 2022.

produkt_name	shipping_date
Ballkleid elegant	2020-07-11
Horus Heresy Collecters Edition	2020-07-11
Glasbläserei Tier: Hase	2020-07-11
Ballkleid elegant	2019-10-22
Horus Heresy Collecters Edition	2019-10-22
Glasbläserei Tier: Hase	2019-10-22
Ballkleid elegant	2021-12-07
Horus Heresy Collecters Edition	2021-12-07
Glasbläserei Tier: Hase	2021-12-07
Cologne Tester Sammlung 1 (10 Flaschen)	2020-07-11
Cologne Tester Sammlung 1 (10 Flaschen)	2019-10-22
Cologne Tester Sammlung 1 (10 Flaschen)	2021-12-07

Please help me figure out what is wrong. It might be something with the db design itself, but I cant really figure out where or how.

Answer 1

You need to do more joins than you are doing. First of all, don´t use than cartesian join because usually runs you to incorrect results. And you need to join with products, orders, invoices and shipping to get your result:

SELECT product.produkt_name, shipping.shipping_date
FROM product
join product_order on product_order.fk_produkt_id=product.product_id
join invoice on invoice.fk_order_id=product_order.order_id
join shipping on shipping.fk_invoice_id=invoice.invoice_id
WHERE shipping.shipping_date BETWEEN '2019-01-01' AND '2021-12-31'

And you can do it in the other way, starting with shippings. It depends on if you have a lot of shippings or not. Both queries return the same result.

SELECT product.produkt_name, shipping.shipping_date
from shipping
join invoice  on shipping.fk_invoice_id=invoice.invoice_id
join product_order on invoice.fk_order_id=product_order.order_id
join product on product_order.fk_produkt_id=product.product_id
WHERE shipping.shipping_date BETWEEN '2019-01-01' AND '2021-12-31'