difficult join query question

[klemens_u]

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

[mgbaron]

Is this what you are looking for?

SELECT inventory.invid,inventory.model,attributes.value as pcname
FROM inventory
LEFT JOIN attributes ON (inventory.invid=attributes.invid AND attributes.attribute="pcname")

[Arnica]

Hi Klemens

You'll need a SELECT sub-clause to get the info you're after.

SELECT inventory.invid, inventory.model, (SELECT attributes.value FROM attributes WHERE (attributes.attribute = 'pcname' AND attributes.invid = inventory.invid)) AS pcname
FROM inventory;

returns:
invid model pcname
1001 HP Notebook
1002 Dell Desktop atpc01
1003 Acer Laptop atpc02
1004 Apple iBook

Regards

Mick