This query works the goal is to show all from CBA and the Dues_YEAR from DUES _PAID which has multiple entries of the CBA_ID. I t returns the right records but it took a couple of minutes!
Nick


SELECT c.CBA_ID
, c.LNAME
, c.FN_MI
, MAX(DUES_YEAR)
FROM CBA AS c
LEFT OUTER
JOIN DUES_PAID AS d
ON d.CBA_ID = c.CBA_ID
GROUP BY d.CBA_ID

could you please do a SHOW CREATE TABLE for each of your tables

i wants to see da indexes

Also, that query won't run because LNAME and FN_MI need to be in the GROUP BY clause, or contained within aggregate expressions... Can you provide the full query?

i think it will give u the error as
because it is not contained in either an aggregate function or the GROUP BY clause.
columns doesn't contain in group clause

no they don't

this is mysql

D'uh, of course
It will run, but the results might not make any sense

in general, they might not, but in this particular case, they obviously will!!

see Debunking GROUP BY myths and the discussion of functional dependence

Sorry but this is what I inherited!

CREATE TABLE `CBA` (
`CBA_ID` int(10) NOT NULL auto_increment,
`access_usr` int(11) unsigned default NULL,
`randomkey_usr` varchar(100) default NULL,
`active_usr` int(25) unsigned default NULL,
`black_balled` varchar(4) default NULL,
`username_usr` varchar(100) default NULL,
`password_usr` tinyint(100) default NULL,
`committee_usr` varchar(100) default NULL,
`s_Generation` int(10) default NULL,
`ID` int(10) default NULL,
`s_GUID` varchar(36) default NULL,
`LNAME` varchar(28) default NULL,
`s_Lineage` blob,
`SN` smallint(5) default NULL,
`FN_MI` varchar(22) default NULL,
`TITLE` varchar(9) default NULL,
`SX` varchar(10) default NULL,
`DJ` varchar(5) default NULL,
`FN_SP` varchar(30) default NULL,
`SPNAME` varchar(30) default NULL,
`SPLNAME` varchar(50) default NULL COMMENT 'Spouse''s Last Name - added 1Oct05',
`ADD1` varchar(50) default NULL,
`ADD2` varchar(50) default NULL,
`City` varchar(30) default NULL,
`ST` char(2) default NULL,
`ZIP` varchar(18) default NULL,
`ADD3` varchar(30) default NULL,
`ADD4` varchar(255) default NULL,
`CITY2` varchar(255) default NULL,
`ST2` varchar(255) default NULL,
`ZIP2` varchar(255) default NULL,
`country` varchar(100) default NULL,
`PHONE1` varchar(255) default NULL,
`PHONE2` varchar(255) default NULL,
`SEARCH_KEY` varchar(255) default NULL,
`EMail` varchar(255) default NULL,
`PR_LABEL` varchar(255) default NULL,
`INFO` varchar(255) default NULL,
`ROLE` varchar(255) default NULL,
`NOTE1` varchar(255) default NULL,
`NOTE2` varchar(255) default NULL,
`NOTE3` varchar(50) default NULL,
`OWNER_1ST` varchar(255) default NULL,
`2_2` varchar(255) default NULL,
`BN1` varchar(255) default NULL,
`SB1` varchar(255) default NULL,
`PORT1` varchar(255) default NULL,
`XNAME1` varchar(255) default NULL,
`FO1` varchar(255) default NULL,
`DS1` varchar(255) default NULL,
`YR1` varchar(255) default NULL,
`BLDTYP1` varchar(255) default NULL,
`BLDLOC1` varchar(255) default NULL,
`L1` varchar(255) default NULL,
`B1` varchar(255) default NULL,
`SA1` varchar(255) default NULL,
`DD1` varchar(255) default NULL,
`1_1` int(10) default NULL,
`HR1` varchar(255) default NULL,
`SOLD1` varchar(50) default NULL,
`UPDATED` datetime default NULL,
`cdate` datetime default NULL,
`edate` date default NULL,
`PRINT` varchar(255) default NULL,
`DUESPD99` varchar(255) default NULL,
`DUESPD98` datetime default NULL,
`DUESPD97` varchar(4) default NULL,
`DUESPD96` varchar(4) default NULL,
`DUESPD95` varchar(255) default NULL,
`MBR_DIED` varchar(255) default NULL,
`SP_DIED` varchar(50) default NULL,
`BN2` varchar(255) default NULL,
`PORT2` varchar(255) default NULL,
`XNAME2` varchar(255) default NULL,
`FO2` varchar(255) default NULL,
`DS2` varchar(255) default NULL,
`SB2` varchar(255) default NULL,
`YR2` varchar(255) default NULL,
`BTYP2` varchar(255) default NULL,
`BLDLOC2` varchar(255) default NULL,
`L2` varchar(255) default NULL,
`B2` varchar(255) default NULL,
`SA2` varchar(255) default NULL,
`DD2` varchar(255) default NULL,
`1_2` int(10) default NULL,
`HR2` varchar(255) default NULL,
`SOLD2` varchar(50) default NULL,
`BN3` varchar(255) default NULL,
`SB3` varchar(255) default NULL,
`PORT3` varchar(255) default NULL,
`XNAME3` varchar(255) default NULL,
`FO3` varchar(255) default NULL,
`DS3` varchar(255) default NULL,
`YR3` varchar(255) default NULL,
`BLDTYP3` varchar(255) default NULL,
`BLDLOC3` varchar(255) default NULL,
`L3` varchar(255) default NULL,
`B3` varchar(255) default NULL,
`SA3` varchar(255) default NULL,
`1_3` int(10) default NULL,
`DD3` varchar(255) default NULL,
`HR3` varchar(255) default NULL,
`SOLD3` varchar(50) default NULL,
`BILL` bigint(1) unsigned default '0',
`WINTER` bigint(1) unsigned default '0',
`SPRING` bigint(1) unsigned default '0',
`FALL` bigint(1) unsigned default '0',
`RESIGNED` varchar(50) default NULL,
`Non_US` bigint(1) unsigned default '0',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`sent_live` int(5) unsigned default NULL,
`Secure_password_usr` varchar(100) default NULL,
`Atempdate_usr` datetime default NULL,
`Attemps_usr` int(11) unsigned default NULL,
`middle_init_usr` varchar(11) default NULL,
PRIMARY KEY (`CBA_ID`),
UNIQUE KEY `s_GUID` (`s_GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Dump of table DUES_PAID
# ------------------------------------------------------------

CREATE TABLE `DUES_PAID` (
`DUES_PAID_ID` int(10) NOT NULL auto_increment,
`CBA_ID` int(10) default NULL,
`CK_NBR` int(10) default NULL,
`BANK_NBR` varchar(50) default NULL,
`AMT_PAID` decimal(19,4) default NULL,
`DATE_OF_DEP` datetime default NULL,
`DUES_YEAR` int(10) default NULL,
`Donation_Comments` varchar(50) default NULL,
PRIMARY KEY (`DUES_PAID_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

the DUES_PAID table has no index on CBA_ID

so let's add one --now try your query again --note the subtle change in the GROUP BY clause