Is there anyone who could help me with this "simple" query.
Did i ask anything in a wrong way because I still got no answers to my
request?
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?
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
