* Thomas Moore
> The issue has to do with relating a PRODUCT table and ATTRIBUTE table so
> that one product is related to multiple attributes. In the
> shopping cart, I
> want to show multiple select menus. After a user selects all of them, only
> the parts related to those attributes will be displayed.
>
> Example:
>
> PRODUCT (product_id, etc.)
> Part #100 - has attributes 1, 2
> Part #200 - has attribute 1
>
> ATTRIBUTE (product_id, attribute_id)
> Part_ID, Attribute - GREEN (1)
> Part_ID, Attribute - Aluminum (2)
>
> Want to select all products from PRODUCT that are both Green and Aluminum.
> In Oracle, I can do this using the INTERSECT command.
>
> select product_id from PRODUCT P, ATTRIBUTE A where P.product_id =
> A.product_id and Attribute = 1
> INTERSECT
> select product_id from PRODUCT P, ATTRIBUTE A where P.product_id =
> A.product_id and Attribute = 2
In mysql you can use two joins to the ATTRIBUTE table:
SELECT P.product_id
FROM PRODUCT P, ATTRIBUTE A1, ATTRIBUTE A2
WHERE
P.product_id = A1.product_id AND A1.Attribute = 1 AND
P.product_id = A2.product_id AND A2.Attribute = 2
> A temporary table may work, but I do not like this as a solution
> as I would have to maintain an extra table with non-relevant data
> (need to constantly insert and delete data).
No, that's the point with a TEMPORARY table... it's a special kind of table,
it is automatically removed when the connection is closed, and separate
connections have separate temporary tables...
<URL: http://www.mysql.com/doc/C/R/CREATE_TABLE.html >
Usefull in many cases, but I do not think you need it in this case.
> I tried playing with outer joins, but I don't know
> how to get it to do this.
The commas in the above select statement is a short form of 'INNER JOIN'.
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
