From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Dev <dev(at)umpa-us(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Join Issues |
Date: | 2003-12-17 01:15:22 |
Message-ID: | 20031216171225.V31260@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2003-12-17 02:23:03 | Re: [pgsql-hackers-win32] UTF support in WIN32 native and Lower/Upper in 7.5 release |
Previous Message | Anthony Best | 2003-12-17 01:12:47 | Sequence question. |