Join Issues

[Dev]

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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match