mysqlphpmyadminxamppmariasql

Getting wrong results on mysql queries


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.


Solution

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