Andreas,
If anyone else is interested, I have solved the problem in stepwise/tutorial fashion
below (best viewed using a
fixed font).
If anyone is skilled in the user of FROM...JOINs, (I'm sure Andreas, and) I'd welcome
a critique/any
improvements!
I have taken a look at this problem, and being more used to the 'old style' of joins
(partly in the WHERE and
partly in the FROM clause), took the opportunity to try to use your style and (better)
memorise how the 'newer'
FROM...JOIN clauses work.
> 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)
Let's get started:
- I built the three tables on my system and populated them with the same sample data
(sometimes it's faster for
me to ask you, but this was quick and easy to do late last night!)
- you want "every attributetype available has to be in the result", so let's start
with the attribute_type
table:
SELECT T.id, T.name, T.unit FROM attribute_type AS T
This produces exactly what you have above.
NB whereas you used "AS at", I have used the alias "T" because AT is a SQL keyword.
Also note that I didn't use
T.* (which would have been easier), because later on we will move individual columns
around the resultset to
'pair' columns used in joins, and eventually need to discard one of each pair - 'which
one' is another question
that has bugged you!
- the many-to-many relationships are handled by the attribute table, so let's bring
that in next. Use a LEFT
JOIN to ensure that every row from the 'left' be listed:
mysql> SELECT T.id, T.name, A.value_id, T.unit, A.type_id, A.Product_id
-> FROM attribute_type AS T
-> LEFT JOIN attribute AS A ON ( T.id = A.type_id );
+----+----------+----------+------+---------+------------+
| id | name | value_id | unit | type_id | Product_id |
+----+----------+----------+------+---------+------------+
| 1 | height | 1 | cm | 1 | 21 |
| 2 | width | 2 | cm | 2 | 32 |
| 2 | width | 3 | cm | 2 | 40 |
| 3 | diameter | 4 | cm | 3 | 21 |
+----+----------+----------+------+---------+------------+
4 rows in set (0.00 sec)
- it's taking shape nicely BUT you said "I only want to have attributes for one
product_id (e.g. 21)" and
looking at your code I spied a WHERE clause, so:
mysql> SELECT T.id, T.name, A.value_id, T.unit, A.type_id, A.Product_id
-> FROM attribute_type AS T
-> LEFT JOIN attribute AS A ON ( T.id = A.type_id )
-> WHERE A.Product_id = 21;
+----+----------+----------+------+---------+------------+
| id | name | value_id | unit | type_id | Product_id |
+----+----------+----------+------+---------+------------+
| 1 | height | 1 | cm | 1 | 21 |
| 3 | diameter | 4 | cm | 3 | 21 |
+----+----------+----------+------+---------+------------+
2 rows in set (0.00 sec)
Oops, we've lost T.id=2 / T.name=width, and you said "every attributetype available
has to be in the result and
any corresponding value or NULL". What's the problem?
The WHERE clause is handled separately/at a different time from the FROM...JOIN, and
so has had a more 'global
effect' than we intended.
- Let's put the condition into the JOIN condition (evaluate it at the same point in
time):
mysql> SELECT T.id, T.name, A.value_id, T.unit, A.type_id, A.Product_id
-> FROM attribute_type AS T
-> LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 );
+----+----------+----------+------+---------+------------+
| id | name | value_id | unit | type_id | Product_id |
+----+----------+----------+------+---------+------------+
| 1 | height | 1 | cm | 1 | 21 |
| 2 | width | NULL | cm | NULL | NULL |
| 3 | diameter | 4 | cm | 3 | 21 |
+----+----------+----------+------+---------+------------+
3 rows in set (0.00 sec)
Bingo - now we're cooking with gas!
- now we can bring in the third table/second JOIN condition:
mysql> SELECT T.name, A.value_id, V.value, T.unit, T.id, A.type_id, A.Product_id
-> FROM attribute_type AS T
-> LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
-> LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );
+----------+----------+-------+------+----+---------+------------+
| name | value_id | value | unit | id | type_id | Product_id |
+----------+----------+-------+------+----+---------+------------+
| height | 1 | 10 | cm | 1 | 1 | 21 |
| width | NULL | NULL | cm | 2 | NULL | NULL |
| diameter | 4 | 222 | cm | 3 | 3 | 21 |
+----------+----------+-------+------+----+---------+------------+
3 rows in set (0.00 sec)
NB I've also shifted T.id a few columns along the resultset table, to make the next
step a bit easier to follow.
- all that's left to do, is to check and note any 'pairs' of columns - discarding the
columns that we have used
to help in 'construction' but which are not required in the final result.
- A.value_id can be quickly removed - it is obviously only needed to join the
attribute and attribute_value
tables, and not to be shown in the result.
- However what of T.id and A.type_id? They are apparently 'the same', ie the
columns/values used to join the
attribute_type and attribute tables. Why aren't they yielding identical values in fact?
- Answer: because of the type of join you wanted "every attributetype available has to
be in the result" even
when the corresponding value is NULL! So under those conditions the attribute table's
type_id can go to NULL
(nothing join-ed) whereas the attribute_type table's id will always be present!
- So:
mysql> SELECT T.name, V.value, T.unit, T.id, A.Product_id
-> FROM attribute_type AS T
-> LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
-> LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );
+----------+-------+------+----+------------+
| name | value | unit | id | Product_id |
+----------+-------+------+----+------------+
| height | 10 | cm | 1 | 21 |
| width | NULL | cm | 2 | NULL |
| diameter | 222 | cm | 3 | 21 |
+----------+-------+------+----+------------+
3 rows in set (0.01 sec)
I'm beaming. Hope it makes you happy,
=dn
PS sorry for the delay, it has taken me a while to type this up during my 'free'
time...
---------------------------------------------------------------------
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
