Re: not enough rows, too many rows, and joins

"bugbear" <bugbear_at_trim_papermule.co.uk_trim> wrote in message news:421c90ec$0$72665$ed2619ec_at_ptn-nntp-reader01.plus.net...
> /*

<snip>
> 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
> );
>
> create table vehicle (
> 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');
> insert into vehicle values (2, 'bicycle', 'black');
> insert into vehicle values (3, 'car', 'blue');
>
> /*
> I would like to run a query, returning the name, and gender,
> and colour of car if they have a car, or null if they don't have
> a car.
>
> The desired result is:
> NAME GENDER COLOUR
> harry male red
> george male null
> priscilla female blue
> sharon female null
<snip>

I don't have Oracle in front of me, but does this work? There are SQL experts on this list who know more than I, but some might suggest you take the question to comp.databases or some other forum instead of comp.databases.theory. --dawn

select name, gender, colour
  from person left outer join vehicle
    on id = owner and type = 'car'; Received on Wed Feb 23 2005 - 19:40:17 CET