INSERT
INTO dietary
( product_id , dietary_id )
VALUES
( 9038 , 6 )
,( 9038 , 10 )
;
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?
yeah, it kinda does matter
the PK is supposed to be composite – not one or the other column, but both of them together
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 
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
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
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?
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?
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
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?
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.
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?
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.
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
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)
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
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.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.