Hi,
I would rather put the attributes.attribute='pcname' in the join clause
It should look something 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' );
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Friday 02 September 2005 14:37, 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
