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.
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'