Join Problem

[Dave Butler]

I am struggling with a join query using MySQL 3.23.31 under AIX 4.3.3. Here 
are the tables involved:

CREATE TABLE sched_acct_cat (
  sched_acct varchar(8) NOT NULL default '',
  linenum int(11) unsigned NOT NULL default '0',
  acct varchar(8) default NULL,
  label varchar(40) default NULL,
  indent smallint(11) unsigned default NULL,
  linetype varchar(8) default NULL,
  pageafter tinyint(3) unsigned default NULL,
  PRIMARY KEY (sched_acct,linenum)
) TYPE=MyISAM;


CREATE TABLE fd (
  acct varchar(8) NOT NULL default '',
  entity varchar(8) NOT NULL default '',
  month varchar(8) NOT NULL default '',
  dataview varchar(8) NOT NULL default '',
  amount double(14,2) default NULL,
  PRIMARY KEY (acct,entity,month,dataview),
  KEY acct(acct,entity,month,dataview)
) TYPE=MyISAM;


The first table is the "structure" I am trying to achieve in the problem 
query. Note line 5 has a NULL where there is no Account.

Here is sample output from the first table:

select CAT.linenum, CAT.acct
from sched_acct_cat CAT
where CAT.sched_acct = 'INC_STMT'
order by CAT.linenum;

1,A8010
2,A8020
3,A8100
4,A8200
5,
6,AT135
7,A8385
... etc

Here is the problem query. It is pulling in a column of FD data.

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




_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php