By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Join Issues

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.