Is there anyone who could help me with this "simple" query, I am not that familiar with multiple joins.
best regards, Andreas > From: Andreas Habereder <[EMAIL PROTECTED]> > Date: Mon Feb 11, 2002 02:21:52 Uhr Europe/Berlin > To: "Manish Mehta" <[EMAIL PROTECTED]> > Subject: Re: SQL-Problem with three tables and joins > > Manish > > thanks for your prompt reply. > > I tried your correction but then I got this: > > your query ( I changed only the table names): > > SELECT at.name, av.value, at.unit, at.id, a.product_id > FROM attribute_type at > LEFT OUTER JOIN attribute a on (at.id = a.type_id) > LEFT OUTER JOIN attribute_value av on (av.id = a.value_id) > WHERE (a.product_id = 21 OR a.product_id IS Not NULL OR a.value_ID > IS Not NULL OR a.type_ID IS Not NULL) > > and the result: > > | name | value | unit | id | product_id | > | height | 10 | cm | 1 | 21 | > | width | 20 | cm | 2 | 32 | > | width | 30 | cm | 2 | 40 | > | diameter | 222 | cm | 3 | 21 | > Number of Results: 4 > > > I only want to have attributes for one product_id (e.g. 21). However, > every attributetype available has to be in the result and any > corresponding value or NULL. > > So my preferred result should look like that: > > | name | value | unit | id | product_id | > | height | 10 | cm | 1 | 21 | > | width | NULL | cm | 2 | NULL | because > width is for this product still empty but should be available to fill > with a value > | diameter | 222 | cm | 3 | 21 | > Number of Results: 3 > > > Any chance to achieve this? > > regards, > Andreas > > > > On Montag, Februar 11, 2002, at 10:19 Uhr, Manish Mehta wrote: > >> Hi, >> Try This >> SELECT at.name, av.value, at.unit, >> at.id, a.product_id >> FROM attribute_type at >> LEFT OUTER JOIN attribute a on (at.id = a.type_id) >> LEFT OUTER JOIN attribute_value av on >> (av.id = a.value_id) >> WHERE (a.product_id = 21 OR a.product_id >> IS Not NULL OR a.value_ID >> IS Not NULL OR a.type_ID IS Not NULL) >> >> Regards >> Manish >> ----- Original Message ----- >> From: Andreas Habereder <[EMAIL PROTECTED]> >> To: <[EMAIL PROTECTED]> >> Sent: Sunday, February 10, 2002 4:04 PM >> Subject: SQL-Problem with three tables and joins >> >> >>> Hi >>> >>> It would be great if anyone could help me with a problem in mySQL. >>> >>> I have 3 tables to describe attributes for a product database. >>> >>> e.g. >>> attribute_type: >>> +----+----------+------+ >>> | id | name | unit | >>> +----+----------+------+ >>> | 1 | height | cm | >>> | 2 | width | cm | >>> | 3 | diameter | cm | >>> +----+----------+------+ >>> >>> attribute_value: >>> +----+----------+ >>> | id | value | >>> +----+----------+ >>> | 1 | 10 | >>> | 2 | 20 | >>> | 3 | 30 | >>> | 4 | 222 | >>> +----+----------+ >>> >>> attribute: >>> +----+----------+----------+------------+ >>> | id | type_id | value_id | product_id | >>> +----+----------+----------+------------+ >>> | 1 | 1 | 1 | 21 | >>> | 2 | 2 | 2 | 32 | >>> | 3 | 2 | 3 | 40 | >>> | 4 | 3 | 4 | 21 | >>> +----+----------+----------+------------+ >>> >>> In a web form where I edit the product, there is also an attribute >>> section where I would like to give certain attributes values specific >>> for the product. The problem is when I associate values to attributes >>> in >>> one product, the same attribute in other products is hidden (not in >>> the >>> result of the query). Therefore I would like to list all >>> attribute_types >>> for each product regardless if there are values assigned or not. >>> >>> I used the following query and I think there must be something wrong: >>> >>> SELECT at.name, av.value, at.unit, at.id, a.product_id >>> FROM attribute_type at >>> LEFT OUTER JOIN attribute a on (at.id = a.type_id) >>> LEFT OUTER JOIN attribute_value av on (av.id = a.value_id) >>> WHERE (a.product_id = 21 OR a.product_id IS NULL OR a.value_ID >>> IS NULL OR a.type_ID IS NULL) >>> >>> regards, >>> Andreas >>> >>> >>> ----------------------------------------- >>> Andreas Habereder >>> private: [EMAIL PROTECTED] >>> mobile: +49 172 838 7771 >>> ----------------------------------------- >>> >>> >>> --------------------------------------------------------------------- >>> 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 >>> >>> >>> >> >> >> > ----------------------------------------- > Andreas Habereder > Kirchenstr. 17e > 81675 München > private: [EMAIL PROTECTED] > fax: +49 1212 5 107 37 317 > mobile: +49 172 838 7771 > ----------------------------------------- > > This message is for the designated recipient only and may contain > privileged, proprietary, or otherwise private information. If you have > received it in error, please notify the sender immediately and delete > the > original. Any other use of the email by you is prohibited. > > --------------------------------------------------------------------- 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
