Klemens Ullmann <[EMAIL PROTECTED]> wrote on 09/02/2005 07:37:43 AM:
> 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
>
That's a simple OUTER JOIN query, like you wrote, with one exception.
By putting the checks for attributes.attribute='whatever' in the WHERE
clause, you forced those values to be part of the results. This in effect
changed your query into an INNER JOIN and eliminated all of the rows from
`model` that didn't have those attributes.
What you wanted to do is to add that comparison into the ON subclause of
your JOIN clause like this:
SELECT inventory.invid,inventory.model,attributes.value as pcname
FROM inventory
LEFT JOIN attributes
ON inventory.invid=attributes.invid
AND attributes.attribute='pcname'
;
Putting it here made it a condition of the JOIN which meant that rows from
the `attributes` table that were not 'pcname' rows would be excluded from
the "right" side of the LEFT JOIN.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
