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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]