MySQL Join 3 table plus calculation

[Daphni]

Hi All,

I have three tables (Sellers, Products and SellerProductsMap that links them thru Seller_ID and Product_ID)I want to display all the sellers from the seller table and show all the products being sold by each seller. However, my boss wants to have a column that shows if a product sold by a seller is good value for money compared to what price other sellers have for the same product.

This is my faulty attempt:
SELECT d.SellerName, t.ProductName p.Price, ((Highest - p.Price)/PRange) 'Value' FROM
Sellers d
INNER JOIN SellerProductsMap p ON
p.Seller_ID = d.Seller_ID
INNER JOIN Products t ON t.Product_ID = p.Product_ID INNER JOIN (SELECT P.Product_ID, P.Seller_ID, min(P.Price) Lowest, MAX(P.Price) Highest,max(P.Price)-min(P.Price) PRange FROM SellerProductsMap P group by P.Product_ID) R ON
R.Product_ID = p.Product_ID order by d.firstname;

I'm using MySQL. I'm probably overcomplicating things! Hopefully someone knows a simple solution

Thanks in advance!

Daphni,

 

[r937]

what's faulty about it?

r937.com | rudy.ca

 

[Daphni]

Its faulty as not all the product's prices are being calculated. So from a result of 30 rows, showing sellers and all there products, only half of the products' prices are being calculated (with null value's on the other products) to return it's value compared to the other sellers products.

Could this be due to linking a "group by product" table with another table that has no group by?

Thanks again!

 

[r937]

well, for one thing, you are missing a comma after t.ProductName, so p.Price could be taken as a column alias (although i think it should've caused an error)

i think maybe there might be some confusion in regards the same table alias P being used both within the derived table subquery, and also in the outer query

let's try rewriting the inner set...

SELECT d.SellerName
     , t.ProductName 
     , p.Price
     , ((R.Highest 
          - p.Price)/R.PRange) 'Value' 
  FROM Sellers d 
INNER 
  JOIN SellerProductsMap p 
    ON p.Seller_ID = d.Seller_ID 
INNER 
  JOIN Products t 
    ON t.Product_ID = p.Product_ID 
INNER 
  JOIN (
       SELECT Product_ID
            , min(Price) Lowest
            , max(Price) Highest
            , max(Price)
             -min(Price) PRange 
         FROM SellerProductsMap  
       group 
           by Product_ID
       ) R 
    ON R.Product_ID = t.Product_ID 
order 
    by d.SellerName

r937.com | rudy.ca

 

[Daphni]

Thanks for your help r937,
The problem was with me and my ignorance of the data. The null values were apearing as there were no other products to compare with for that particular product.