List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:December 14 2001 2:53pm
Subject:Re: Join Problem
View as plain text  

Dave Butler wrote:

> I am struggling with a join query using MySQL 3.23.31 under AIX 4.3.3. 
> Here  are the tables involved:
> 
> select CAT.linenum, CAT.acct, FD.amount
> from sched_acct_cat CAT LEFT JOIN fd FD
> ON CAT.acct = FD.acct
> where CAT.sched_acct = 'INC_STMT'
> AND FD.entity='FMCI'
> AND FD.dataview='ACTUAL.Y'
> AND FD.month='OCT01'
> order by CAT.linenum;
> 
> Here is the output. Line 5 is missing because of the NULL. Line 4 is 
> missing  because Account A8200 is 0 for FMCI because it is missing 
> from the fd table.
> 
> 1,A8010,99999
> 2,A8020, 99999
> 3,A8100, 99999
> 6,AT135, 99999
> 7,A8385, 99999
> 8,A8600, 99999
> 9,A8800, 99999
> 10,A8900, 99999
> 12,AT140, 99999
> .... etc.
> 
> I thought the 'LEFT JOIN' clause would keep all the lines and simply 
> leave  NULLs where it could not provide data. The books I looked at 
> seem to imply  this.
> 
> Thanks for any help on this.
> 
> Dave
> 
Except, FD.entity  will also be NULL, which will cause the AND to fail.


> 

Thread
Join ProblemDave Butler14 Dec
  • Re: Join ProblemGerald Clark14 Dec