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

<ford_desperado_at_yahoo.com> wrote in message news:1109177418.014873.95560_at_f14g2000cwb.googlegroups.com...
> select
> person.name, person.gender,
> (select min(vehicle.colour) from vehicle where person.id =
> vehicle.owner and vehicle.type = 'car') color
> from
> person
>
> if you don't like min, feel free tor eplace it with anything else, just
> make sure the subselect always returns 1 value

I wouldn't normally post a solution to an SQL problem on c.d.t, but the above "solution" provokes me sufficiently to insist that this is more obvious, natural, and in some sense "correct":

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

   person left outer join vehicle i
   on person.id = vehicle.owner and vehicle.type = 'car'

And to the OP, you might want to review the difference between a joining condition in an ON clause, and a restriction on the final result in a WHERE clause, which is the root of your puzzlement. (Though being puzzled about SQL is nothing to be ashamed of.)

Roy Received on Wed Feb 23 2005 - 18:02:53 CET