Re: Join Issues

On Tue, 16 Dec 2003, Dev wrote:

> Hello all,
>
> I have been working with joins and having alot of success up until now.
>
> What I have is this:
> SELECT a.merno
>   ,g.mcmid
>   FROM (
>    total AS a LEFT JOIN mcmid AS g ON (g.merno=a.merno))
>   WHERE a.repno='111111'
>   AND a.month='2003-11-01'
>   AND g.month='2003-11-01'
>   ORDER BY merno
>
> Currently it is returning only 178 records where it should be returning 407
> records.
> The 401 records are what are returned from the total table.
>
> I beleave the problem is with the:
> AND g.month='2003-11-01'
>
> any clues?

By saying g.month = '2003-11-01' in the where you've effectively removed
the outerness of the join. If there's no matching g row for
g.merno=a.merno, it extends the a row with nulls for the g column and then
will be checking g.month='2003-11-01' which will return unknown because
the g row has a null for month.  Depending on the behavior you want,
either you'd want AND (g.month is null or g.month='2003-11-01') in the
where or you want the month clause in the ON at which point it's taken
into account for determining if there's a matching row.