difficult join query question

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

 

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")

Code (sql):

 

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;

Code (sql):

returns:

 
[FONT=Courier New]invid  model          pcname[/FONT]
[FONT=Courier New]1001   HP Notebook[/FONT]
[FONT=Courier New]1002   Dell Desktop   atpc01[/FONT]
[FONT=Courier New]1003   Acer Laptop    atpc02[/FONT]
[FONT=Courier New]1004   Apple iBook[/FONT]

Code (markup):

Regards

Mick