Klemens Ullmann wrote:
hello!
I've got two tables for an IT hardware inventory:
### table inventory:
invid model
-------------------
1001 HP Notebook // no attributes
1002 Dell Desktop // only one attribut 'pc-name'
1003 Acer Laptop // attributes 'pc-name' & 'harddisk'
1004 Apple iBook // only one attribut 'harddisk'
### table attributes:
id invid attribute value
-----------------------------
501 1002 pcname atpc01
502 1003 pcname atpc02
503 1003 harddisk 20GB
504 1004 harddisk 40GB
what I want is a list of all computers (without exeptions) in the
following form:
invid - modell - pcname
my best guess zu date of a possible query is:
SELECT inventory.invid,inventory.model,attributes.value as pcname
FROM inventory
LEFT JOIN attributes ON (inventory.invid=attributes.invid)
WHERE attributes.attribute='pcname' or attributes.attribute is NULL;
### result:
invid model pcname
---------------------------
1001 HP Notebook NULL
1002 Dell Desktop atpc01
1003 Acer Laptop atpc02
now my problem are the missing computers which have an attribute, but
not a 'pc-name'-attribute.
(in the example above the missing iBook)
thank you for suggestions how to solve the problem!
have a nice day,
klemens ullmann / vienna
Quite a fun challenge! Using the test data you gave, this query works,
assuming that you are running a version of MySQL that can do subqueries.
SELECT
inventory.invid
, model
, value AS pcname
FROM inventory
LEFT JOIN
( SELECT invid, value FROM attributes WHERE attribute='pcname' ) AS a
USING(invid);
+-------+-----------------+--------+
| invid | model | pcname |
+-------+-----------------+--------+
| NULL | HP Notebook | NULL |
| 1002 | Dell Desktop | atpc01 |
| 1003 | Acer Laptop | atpc02 |
| 1004 | Apple iBook | NULL |
+-------+-----------------+--------+
4 rows in set (0.00 sec)
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
