CODEWITHSUNDEEP
mysql
user3237076
user3237076

Reputation: 45

Or operator returns more rows then expected mysql

I have a table with some items that are related to car models. Those items could have several categories. I have to select all of them that are related to cars and in all given categories categories for each item stored in sc_products table.

Here is my query:

SELECT 
t15_catalogue_line.
T15_GROUP, 
sc_products.product_code,
sc_products.unic, sc_products.name_ru, UPPER(TRIM(sc_products.brief_description_ru)) AS brief_description_ru, sc_products.suupplier, price.Price, sc_group_discounts.`action`, sc_group_discounts.procent, sc_products.productID, price.in_stock, price.supplier, t10_item.T10_ITEM, t10_item.unic, t10_item.T10_DESC, t10_item.T10_IMG, t10_item.T10_ITEM_GROUP, t10_item.T10_FIELD1, t10_item.T10_FIELD2, t10_item.T10_FIELD3, t10_item.T10_FIELD4, t10_item.T10_FIELD5, t10_item.T10_FIELD6, t10_item.T10_FIELD7, t10_item.T10_FIELD8, t10_item.T10_FIELD9, t10_item.T10_FIELD10, t10_item.T10_FIELD11, t14_item_fields.T14_ITEM_GROUP, t14_item_fields.T14_FIELD, t14_item_fields.T14_NAME, t14_item_fields.T14_UNIT, t14_item_fields.T14_SEARCH 
FROM 
sc_products 
LEFT OUTER JOIN t15_catalogue_line ON (sc_products.unic = t15_catalogue_line.unic) 
LEFT OUTER JOIN price ON (sc_products.unic = price.unic) AND (sc_products.suupplier = price.postavchik) 
LEFT OUTER JOIN sc_group_discounts ON (sc_products.item_group = sc_group_discounts.item_group) 
LEFT OUTER JOIN t10_item ON (sc_products.unic = t10_item.unic and sc_products.CatText=t10_item.CatText and sc_products.brief_description_ru=t10_item.brand) 
LEFT OUTER JOIN t14_item_fields ON (t10_item.T10_ITEM_GROUP = t14_item_fields.T14_ITEM_GROUP) 
WHERE 
sc_products.CatText = 'bracke mechanism' or 
sc_products.CatText='bracke montage ' or 
sc_products.CatText='hydraulic repair ' AND   
t15_catalogue_line.T15_CARTYPE = '30442' AND 
t15_catalogue_line.T15_GROUP = '666' and 
sc_products.unic is not null and 
sc_products. unic!='' 
GROUP BY sc_products.product_code,sc_products.brief_description_ru, sc_products.suupplier 
ORDER BY ISNULL( price.price),price.price ASC

Query returns all items in those caegories and they aren't related to auto so table t15_catalog line doesn't take part in query what could be the solution

Upvotes: 0

Views: 36

Answers (3)

jpw
jpw

Reputation: 44891

This logic:

a OR b OR c AND d is equivalent to a OR b OR (c AND d) and to not (a OR b OR c) AND d which seems to be what you want.

You need to add parentheses around the OR block like this:

WHERE 
(
    sc_products.CatText = 'bracke mechanism'
 OR sc_products.CatText = 'bracke montage ' 
 OR sc_products.CatText = 'hydraulic repair ' 
)
AND t15_catalogue_line.T15_CARTYPE = '30442' 
AND t15_catalogue_line.T15_GROUP = '666' 
AND sc_products.unic is not null 
AND sc_products. unic!=''

Upvotes: 1

Jonny
Jonny

Reputation: 1171

You probably want to use paranthesis in your query for the desired result. How about 

SELECT 
    t15_catalogue_line.
    T15_GROUP, 
    sc_products.product_code,
    sc_products.unic, sc_products.name_ru, 
    UPPER(TRIM (sc_products.brief_description_ru)) AS brief_description_ru,
    sc_products.suupplier, price.Price, sc_group_discounts.`action`,
    sc_group_discounts.procent, sc_products.productID,
    price.in_stock, price.supplier, t10_item.T10_ITEM, t10_item.unic,
    t10_item.T10_DESC, t10_item.T10_IMG, t10_item.T10_ITEM_GROUP,
    t10_item.T10_FIELD1, t10_item.T10_FIELD2, t10_item.T10_FIELD3,
    t10_item.T10_FIELD4, t10_item.T10_FIELD5, t10_item.T10_FIELD6,
    t10_item.T10_FIELD7, t10_item.T10_FIELD8, t10_item.T10_FIELD9, 
    t10_item.T10_FIELD10, t10_item.T10_FIELD11,
    t14_item_fields.T14_ITEM_GROUP, t14_item_fields.T14_FIELD,
    t14_item_fields.T14_NAME, t14_item_fields.T14_UNIT,
    t14_item_fields.T14_SEARCH 
FROM 
    sc_products 
LEFT OUTER JOIN t15_catalogue_line ON 
    (sc_products.unic = t15_catalogue_line.unic) 
LEFT OUTER JOIN price ON 
    (sc_products.unic = price.unic) 
    AND (sc_products.suupplier = price.postavchik) 
LEFT OUTER JOIN sc_group_discounts ON 
    (sc_products.item_group = sc_group_discounts.item_group) 
LEFT OUTER JOIN t10_item ON 
    (sc_products.unic = t10_item.unic and
    sc_products.CatText=t10_item.CatText and
    sc_products.brief_description_ru=t10_item.brand) 
LEFT OUTER JOIN t14_item_fields ON 
    (t10_item.T10_ITEM_GROUP = t14_item_fields.T14_ITEM_GROUP) 
WHERE 
    (
        sc_products.CatText = 'bracke mechanism' 
    or  
        sc_products.CatText='bracke montage ' 
    or 
        sc_products.CatText='hydraulic repair '
    )
    AND t15_catalogue_line.T15_CARTYPE = '30442' 
    AND t15_catalogue_line.T15_GROUP = '666' 
    and sc_products.unic is not null 
    and sc_products. unic!='' 

 GROUP BY 
     sc_products.product_code,sc_products.brief_description_ru,
     sc_products.suupplier ORDER BY ISNULL( price.price),
     price.price ASC

Upvotes: 0

Jester
Jester

Reputation: 1408

I highly suspect that the missing parentheses () around the multiple AND is your problem.

Query returns all items in those caegories and they aren't related to auto so table t15_catalog line doesn't take part in query what could be the solution

Your grouping logic now does exactly what you describe, below is what i think you meant to do:

WHERE (sc_products.CatText = 'bracke mechanism' or sc_products.CatText='bracke montage ' or sc_products.CatText='hydraulic repair ') AND t15_catalogue_line.T15_CARTYPE = '30442' AND t15_catalogue_line.T15_GROUP = '666' and sc_products.unic is not null and sc_products. unic!='' GROUP BY sc_products.product_code,sc_products.brief_description_ru, sc_products.suupplier ORDER BY ISNULL( price.price),price.price ASC

Upvotes: 1

Related Questions