MySQL join to pivot question

Question

I'm sure this is simple but my brain just isnt working today!

I have a table products, lets just assume it contains p_id, i have another table - a pivot table - which references between products and another table attributes, this table is products_to_attributes and contains pta_aid (attribute id) and pta_pid (product id)

Hopefully this (incorrect) query will show what i want to do better than i can explain it:

SELECT `p_id` FROM `products` 
LEFT JOIN `products_to_attributes` ON (`pta_pid` = `p_id`)
WHERE ((`pta_aid` = '1' OR `pta_aid` = '2') AND(`pta_aid` = '3'))

I want to be able to group together attributes where a product must have attribute 1 Or attribute 2 AND have attribute 3.

same field in same row can not have two values

Shakti Singh
– Shakti Singh

2011-04-01 11:25:54 +00:00
Commented Apr 1, 2011 at 11:25 — Shakti Singh
– Shakti Singh, Commented Apr 1, 2011 at 11:25

roberttdev · Accepted Answer · 2011-04-01 11:34:20Z

2

If I understand your need correctly, you pretty much have to have two EXISTS clauses:

SELECT p_id FROM products PR
WHERE EXISTS (SELECT p_id FROM products_to_attributes WHERE (pta_aid = 1 OR pta_aid=2) AND pta_pid=PR.p_id )
AND EXISTS (SELECT p_id FROM products_to_attributes WHERE pta_aid = 3 AND pta_pid=PR.p_id)

edited Apr 1, 2011 at 11:34

answered Apr 1, 2011 at 11:29

roberttdev

47.2k2 gold badges23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

small_ticket · Accepted Answer · 2011-04-01 11:29:33Z

0

SELECT p.`p_id` FROM `products` p
LEFT JOIN `products_to_attributes` pta ON (p.`p_id` = pta.`pta_pid`)

Name the tables and specify which field is from which table. However how a field can be both 1 and 3 in a row? there is a logical problem in your where clause.

answered Apr 1, 2011 at 11:29

small_ticket

2,0505 gold badges23 silver badges31 bronze badges

1 Comment

robjmills Over a year ago

I appreciate the logic problem but my code was essentially pseudo-code to explain the issue

Collectives™ on Stack Overflow

MySQL join to pivot question

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related