Re: LEFT JOIN Woes

From: Per Larsen (perl.news_at_tiscali.no)
Date: 03/27/05 

Date: Sun, 27 Mar 2005 17:01:50 +0200

Don wrote:
> The way I understand it, a LEFT JOIN uses all the records in the "left"
> table, but only those record in "right" table that link to the left table.
> So if I have something like:
>
> ....
> FROM tblPosition AS P LEFT JOIN tblPerson AS R ON P.PositionNumber =
> R.PositionNumber
> ...
>
> the result of the query should have the same number of records as in
> tblPosition. (For the sake of this example, say there are 10 positions and
> 8 persons.)

This is generally not the case. If all records in the 'right' table were related to *only* one record in the 'left' table, you would have this record from the 'left' table repeated for each occurence in the 'right' table, and only a single record from each of all the others, e.g.:

  10 records of different kind of fruits in the 'left' table
   5 records of sale of the same kind of fruit

would give you 14 records, 9 with fruits with no sale, 5 with the one type sold.

Hth
PerL