'AND' 'OR' not working together

mysql

#41

INSERT 
  INTO dietary 
     ( product_id , dietary_id ) 
VALUES 
     ( 9038 ,  6 )
    ,( 9038 , 10 )
;

#42

The table's now set like that but I couldn't make either of the coulis primary keys as it said they had to contain unique entries. Does that matter?


#43

yeah, it kinda does matter

the PK is supposed to be composite -- not one or the other column, but both of them together


#44

Okay, created a brand new table and was able to have them both as primary keys then. I then added the data to it and think I've done it right this time: dietary.sql.zip (4.9 KB)

I know you said I need to rewrite the query to make this work but that's where my problems first arose :frowning: This is what I was using when I had the separate tables:

SELECT product_name
     , weight
     , weight_id
     , serving
     , price
     , brand_name
     , protein_100
     , fat_100
     , calories_100
     , carbs_100
     , aw_deep_link
     , product_feed_id
     , image_url
     , best_seller 
  from (((((((
       feeds 
INNER 
  JOIN timing 
    ON feeds.product_feed_id = timing.product_id
       ) 
INNER 
  JOIN flavour 
    ON feeds.product_feed_id = flavour.product_id
       ) 
INNER 
  JOIN sweeteners 
    ON feeds.product_feed_id = sweeteners.product_id
       ) 
INNER 
  JOIN source 
    ON feeds.product_feed_id = source.product_id
       ) 
INNER 
  JOIN dietary 
    ON feeds.product_feed_id = dietary.product_id
       ) 
INNER 
  JOIN ingredients 
    ON feeds.product_feed_id = ingredients.product_id
       ) 
INNER 
  JOIN product_categories_map 
    ON feeds.product_feed_id = product_categories_map.product_id
       ) 
 WHERE brand_name IN ("Youngs"
                    , "Birds Eye" 
                    , "Own brand") 
   and weight_id IN ("2"
                   , "0") 
   and ingredients.ingredients_id IN ("12"
                                    , "3"
                                    , "9") 
   and price >=0 
   and price <=250 
   and enabled=1 
   and stock=1 
   and deleted=0 
ORDER 
    BY best_seller DESC LIMIT 20

#45

i would just like to comment on this --

WHERE brand_name IN ("Youngs"
                    , "Birds Eye" 
                    , "Own brand") 
   and weight_id IN ("2"
                   , "0") 
   and ingredients.ingredients_id IN ("12"
                                    , "3"
                                    , "9")

suppose you have a product that has 5 brands, 3 weights, and 7 ingredients

you are joining all of them to their product row, which means you will see at least 105 rows in the result set -- more, if there are multiples of other relationships

as i mentioned in post #15 and #17, it seems somewhat misguided to try to show these

furthermore, just tossing a GROUP BY clause at the problem does not make it go away, you will see only one row per product but underneath the covers it's still returning 105 rows from the database and the attributes shown will be indeterminate -- a random one from each table

you really need to rethink what you're trying to retrieve


#46

I want to be able to narrow results down by filters so for example if somebody selects Birds Eye as a brand and then say egg as an ingredient it will only show the products where the brand is Birds Eye and it has egg. I'm really struggling to get the query right, I thought I was heading int he right direction with my query but I guess I'm getting further away? :frowning:


#47

your WHERE clause simply filters which products to show

but the chosen products will be returned with all their attributes!

did you understand the part about 5 x 3 x 7 = 105?


#48

That makes perfect sense and helps me understand exactly where I was going wrong, instead of narrowing it down every time I was just adding to it. I thought that if you wanted to only get certain results then you had to use WHERE, I tried to change the where to IF but got an error :frowning:


#49

i don't think you're listening very carefully

the WHERE clause is fine -- it simply filters out which products you want to see

those products which satisfy the WHERE conditions are then presented with all their attributes

did you understand the 5 x 3 x 7 = 105 thing?


#50

I think I misunderstood what you were saying, I thought you were saying because of the WHERE clause it was getting everything. From my understanding then I need to get the id's of the attributes where values match and then only select those results from the feeds table? And I was doing in the other way round. I do understand what I'm trying to do, but just not how to do it.


#51

have you run the query at all lately? like, the query in post #44?

how many total rows did it return?

and ballpark estimate, how many rows per product?


#52

After a bit of tweaking it's now almost working. I can't tell you how happy I am. The only thing is this returned 20 results but they were all the same product, so do I need to add the GROUP BY back so that it only shows one product rather than the same one twenty times? I did add it back and it showed just one, but I didn't know if that was the correct way of doing it or if using SELECT DISTINCT was better?

SELECT product_name
     , weight
     , weight_id
     , serving
     , price
     , brand_name
     , protein_100
     , fat_100
     , calories_100
     , carbs_100
     , aw_deep_link
     , product_feed_id
     , image_url
     , best_seller 
  from (((((((
       feeds 
INNER 
  JOIN timing 
    ON feeds.product_feed_id = timing.product_id
       ) 
INNER 
  JOIN flavour 
    ON feeds.product_feed_id = flavour.product_id
       ) 
INNER 
  JOIN sweeteners 
    ON feeds.product_feed_id = sweeteners.product_id
       ) 
INNER 
  JOIN source 
    ON feeds.product_feed_id = source.product_id
       ) 
INNER 
  JOIN dietary 
    ON feeds.product_feed_id = dietary.product_id
       ) 
INNER 
  JOIN ingredients 
    ON feeds.product_feed_id = ingredients.product_id
       ) 
INNER 
  JOIN product_categories_map 
    ON feeds.product_feed_id = product_categories_map.product_id
       ) 
 WHERE brand_name IN ("Youngs"
                    , "Birds Eye" 
                    , "Own brand") 
   and weight_id IN ("3"
                   , "0") 
   and ingredients.ingredients_id IN ("12"
                                    , "1"
                                    , "4") 
   and price >=0 
   and price <=250 
   and enabled=1 
   and stock=1 
   and deleted=0 
ORDER 
    BY best_seller DESC LIMIT 20

I can't thank you enough for helping me with this, you've also helped me to understand MySQL better too so once again thank you!! I couldn't have done it without you're help.


#53

this has been an incredibly long thread and if you would kindly scroll back and re-read it all, you will see that i have been talking about this problem all the way through

for example, "did you understand the 5 x 3 x 7 = 105 thing?"

i repeat, i think the approach in this query is misguided

slapping GROUP BY onto it is ~not~ the way to solve this problem


#54

I'm sorry I don't quite understand what you're saying, are you saying the query I'm using isn't the best way of doing it or that it's completely wrong.

I've recreated all of the attribute tables but haven't changed the feeds one so am going to redo that to taking in your advice and having primary keys for product_name and brand_name that way if the same product is sold by different companies it'll show them but won't if they same company has the product more than once (maybe with different weights etc)


#55

let's give an example...

say you have product XYZ which has 5 flavours, 3 sources, and 7 ingredients

your query will produce 105 rows for product XYZ -- please tell me you understand this

if not, you may google "cross join effects"

there are also several threads here on sitepoint which talk about this -- read them carefully

Problem with 2 LEFT JOINS and GROUP BY

SUM across multiple tables

Display topics that reside in two junction tables


#56

I thought I did understand but and going to have read of those links to make sure I full do before moving on. Thanks again for your help with this.


#57

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.