Interesting problem with conditional join

Hello,

consider this simple inventory tool:

here are some notebooks, each with an unique inventory number:

create table inv_hw_test (
invid int(11) NOT NULL auto_increment,
hw_type varchar(30),
serial varchar(30),
PRIMARY KEY (invid)
);

insert into inv_hw_test values (1000,"notebook","12345");
insert into inv_hw_test values (1001,"notebook","23456");
insert into inv_hw_test values (1002,"notebook","34567");

and here are attributes for the notebooks above, linked to the inventory number:

create table inv_attr_test (
attrid int(11) NOT NULL auto_increment,
invid int(11),
name varchar(30),
value varchar(30),
PRIMARY KEY (attrid)
);

insert into inv_attr_test values (1,1001,"harddrive","120");
insert into inv_attr_test values (2,1001,"display","15");
insert into inv_attr_test values (3,1001,"ram","512");
insert into inv_attr_test values (4,1002,"display","15");
insert into inv_attr_test values (5,1002,"ram","512");

notebook 1000 has no attributes, notebook 1001 has 3 attributes and so on.

now I want to retreive all notebooks that have the attribute "harddrive"
I expected the following query to do exactly that:

select hw.invid, hw.hw_type, hw.serial, attr.name, attr.value
from inv_hw_test hw
left join inv_attr_test attr on (
hw.invid = attr.invid
and attr.name = 'harddrive'
)
;

but it does not. here is the result:
+-------+----------+--------+-----------+-------+
| invid | hw_type | serial | name | value |
+-------+----------+--------+-----------+-------+
| 1000 | notebook | 12345 | NULL | NULL |
| 1001 | notebook | 23456 | harddrive | 120 |
| 1002 | notebook | 34567 | NULL | NULL |
+-------+----------+--------+-----------+-------+
3 rows in set (0.00 sec)

I expected only one line, the one with 1001.

But why?
By the way I'm using Mysql 5.0 on Ubuntu 6.06 LTS

Thank you for you help!

Kind regards from Vienna / Austria,

Klemens

 

It's not a problem, that's how it's supposed to work. Try this:

select hw.invid, hw.hw_type, hw.serial, attr.name, attr.value
from inv_hw_test hw, inv_attr_test attr
where hw.invid = attr.invid and attr.name = 'harddrive'

Code (markup):

Left join returns ALL rows from the first table even if there are no matches in the second table (NULL values).