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

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.

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.

CodePudding user response：

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'