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