0

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

Google
  • 23
  • 1
  • 10
  • In your `WHERE` clause, you are asking only for Product 1. It will never show Product 2. – Sablefoste Feb 14 '15 at 09:54
  • Yes, later i will have more Stores. So i want to know What Stores have the Product 1 and at what Price. + is no sense relating the prices with 1 product. I edit the Mistake i made with the resaults – Google Feb 14 '15 at 09:58

2 Answers2

0

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 |
jpw
  • 44,361
  • 6
  • 66
  • 86
0

What you want is to use JOIN combined with ON

SELECT StoreName, Price, Product Name 
FROM tblStores
JOIN tblProduct ON tblStores.StoreID = tblProducts.StoreID
JOIN tblProductDetails ON tblProduct.ProductID = tblProductDetails.ProductID
WHERE ProductName = 'Product 1'

You may consider GROUP BY to identify the specific stores.

Sablefoste
  • 4,032
  • 3
  • 37
  • 58
  • I made a mistake to the expecting results. I fixed it now. But when i add more Stores. Is giving For Every Store Each Product with Every Possible Price. – Google Feb 14 '15 at 10:04