Search code examples
mysqldatabasetable-relationships

MYySQL is the Table Relationship wrong?

I'm very new in Databases and more specific in MYSQL. I use xampp + MySQL Workbench. I make 3 tables using MySQL Workbench:

- tbStores with fields StoreID(PK-INT-AI), StoreName - tbProducts with fields ProductID(PK-INT-AI), ProductName - tbProductDetails with fields ProductDetailID(PK-INT-AI), Price, ProductID(FK), StoreID(FK)

*PK=Primary Key
*INT=Numeric Type Attributes
*AI=Auto Increments

In case you don’t understand the Relationships above:

  • 1 to many From tbStores(StoreID) To tbProductDetails (StoreID)
  • 1 to many From tbProducts(ProductID) To tbProductDetails (ProductID)

I add values to the fields:

- tbStores=> StoreName=> Store 1 - tbProducts=> ProductName=> Product 1, Product 2 - tbProductDetails=> Price=> 50, 30 - tbProductDetails=> ProductID=> 1, 2 - tbProductDetails=> StoreID=> 1, 1

To the Query:
SELECT tbStores.StoreName, tbProductDetails.Price, tbProducts.ProductName FROM tbStores, tbProductDetails, tbProducts Where ProductName = 'Product 1';

The Problem:
Query will return this

Store 1, 50, Product 1
Store 1, 30, Product 1

Is giving me Same Product with 2 different Prices. What I was expecting to take was this :

Store 1, 50, Product 1

What am I doing wrong? I believe it has to do with relationships but I can't figure it out.
Thanks

Solution

  • You need to join the tables together (specify how they are related) in the query, the query should look something like this:

    SELECT tbStores.StoreName, tbProductDetails.Price, tbProducts.ProductName 
FROM tbProductDetails 
JOIN tbStores ON tbStores.StoreID = tbProductDetails.StoreID
JOIN tbProducts ON tbProducts.ProductID = tbProductDetails.ProductID
WHERE tbProducts.ProductName = 'Product 1';

    If you want all products you have to remove the where clause. Note that I took the liberty of changing your implicit joins in the from clause to explicit joins using the join keyword.

    Sample SQL Fiddle

    Sample output:

    | STORENAME | PRICE | PRODUCTNAME |
|-----------|-------|-------------|
|    Store1 |    50 |    Product1 |