not enough rows, too many rows, and joins

In my real example, my data is normalised (e.g. vehicle type is not a varchar ;-)

My question is towards the end, and this entire posting is directly runnable under Oracle for the greater convenience of all, since I've put all text in comments...

Any suggestions keenly listened to.

   BugBear
*/

create table person (

         id integer not null primary key,
         name varchar(30) not null unique,
         gender char(6) not null,
         age integer not null

         owner integer not null references person(id),
         type char(10) not null,
         colour char(10) not null

insert into person values (1, 'harry', 'male', 34);
insert into person values (2, 'george', 'male', 23);
insert into person values (3, 'priscilla', 'female', 51);
insert into person values (4, 'sharon', 'female', 20);

insert into vehicle values (1, 'bicycle', 'green');
insert into vehicle values (1, 'car', 'red');

NAME                 GENDER  COLOUR
harry                male    red
george               male    null
priscilla            female  blue
sharon               female  null

The difficulties include:
not getting 2 rows for harry (who has 2 vehicles) getting any rows at all for sharon (who has 0 vehices) getting one row at for george (who has 1 vehice, but it's not a car)

The "obvious" query...
*/

select

         person.name, person.gender, vehicle.colour from

         person inner join vehicle on person.id = vehicle.owner where

         vehicle.type = 'car';

/*

returns just people with cars:

NAME                           GENDER COLOUR
------------------------------ ------ ----------
harry                          male   red
priscilla                      female blue

Changing to an outer join (and allowing null on vehicle type) nets us the vehicle-less sharon
*/

select

         person.name, person.gender, vehicle.colour from

         person left outer join vehicle on person.id = vehicle.owner where

         vehicle.type is null or vehicle.type = 'car';

/*

which returns people with cars and people with no vehicle, but not people who have some other type of vehicle but no car:

NAME                           GENDER COLOUR
------------------------------ ------ ----------
harry                          male   red
priscilla                      female blue
sharon                         female

drop table vehicle;
drop table person; Received on Wed Feb 23 2005 - 15:17:43 CET