Search code examples
sqlsql-serverview

Getting strange results from view query

I am testing a database that was written by someone else who left company.
Here what it looks like:

enter image description here

Here is the query:

SELECT DISTINCT 
       bcprod.product.item_number,
       bcprod.product.product_id,
       bcprod.product_price.price1,
       bcprod.product_price.price2,
       bcprod.product_price.price3,
       bcprod.product_price.price4,
       bcprod.product_price.option_value_id,
       bcprod.product_option.weight_class,
       bcprod.product_option.status
FROM bcprod.product
     INNER JOIN bcprod.product_price ON bcprod.product_price.product_id = bcprod.product.product_id
     INNER JOIN bcprod.product_option ON bcprod.product_option.product_id = bcprod.product.product_id
WHERE bcprod.product.is_sale = 1
  AND bcprod.product.status = 1
  AND bcprod.product_option.status = 1
 AND bcprod.product.product_id = 1

The problem is when I check the weight column in product_option I get 815 rows back compared to 285 with weight not checked(when testing without the last AND in where clause). I save the design view every time I make a change - I even refresh it afterwards. I checked for duplicates in both the product_price and product_option table because I thought there was something wrong with option_value_id and product_id:

select bcprod.product_price.product_id,
       bcprod.product_price.option_value_id,
       Count(*)
       from bcprod.product_price
group by bcprod.product_price.product_id,
         bcprod.product_price.option_value_id
having (Count(*)>1)

I found no duplicates - I need some advice as to how to fix this please - I am not that good at working with databases. The option_value_id gets doubled and if there was only two rows for an item - after checking weight it doubles to like 4 rows - as an example?

Incorrect Data: enter image description here

correct data - without weight:

enter image description here

Solution

  • I was able to fix the problem by modifying the query with:

    SELECT DISTINCT 
        bcprod.product.item_number,
        bcprod.product.product_id, 
        bcprod.product_price.price1, 
        bcprod.product_price.price2,
        bcprod.product_price.price3, 
        bcprod.product_price.price4, 
        bcprod.product_option.status AS option_status, 
        bcprod.product_option.weight_class,
        bcprod.product.category_id,
        bcprod.product_option.weight, 
        bcprod.product_price.option_value_id,
        bcprod.product.weight AS accessesory_weight, 
        bcprod.product.weight_class AS accessesory_weightclass, 
        bcprod.product.price, 
        bcprod.product.status AS main_status,
        bcprod.product.is_sale, 
        bcprod.product.is_accessory,
        bcprod.product.image, 
        bcprod.option_value.option_name
FROM            bcprod.product INNER JOIN
        bcprod.product_price ON bcprod.product_price.product_id = 
        bcprod.product.product_id INNER JOIN
        bcprod.product_option ON bcprod.product_option.product_id = 
        bcprod.product.product_id AND bcprod.product_price.option_value_id = 
        bcprod.product_option.option_value_id INNER JOIN
        bcprod.option_value ON bcprod.product_price.option_value_id = 
        bcprod.option_value.option_value_id AND 
        bcprod.product_option.option_value_id = 
        bcprod.option_value.option_value_id
 WHERE        (bcprod.product.status = 1)

    If someone could come with better idea - I'm all ears. The problem was in last join - adding the AND.