Joins not producing wanted fields

Databases
1

I am trying to return donor info by donor name with a list of his/her gifts of goods or cash. The four tables are the donor info, gifts, receipts and events. The event id number will be a post entry from a select form. The query I have only returns two rows.

SELECT 
CONCAT(donor_info.lastname,', ',donor_info.firstname) AS fullname
, donor_info.d_id AS donorid
, donor_info.emailp AS email
, donor_gifts.d_id AS donorgiftid
, donor_gifts.gift_id AS giftid
, donor_gifts.date AS gift_date
, donor_gifts.goods AS gifts
, donor_receipts.d_id AS donorrecid
, donor_receipts.rec_id AS rec_id 
, donor_receipts.date AS receipts_date
, donor_receipts.amt AS amount
, donor_events.ev_name AS event
FROM donor_info,donor_events
INNER JOIN donor_gifts ON `donor_info`.`d_id`=`donor_gifts`.`d_id`
INNER JOIN donor_receipts ON `donor_info`.`d_id`=`donor_receipts`.`d_id`
WHERE  `donor_events`.`ev_id`=`donor_receipts`.`ev_id`
AND `donor_events`.`ev_id`=`donor_gifts`.`ev_id`
AND `donor_events`.`ev_id`=7 
ORDER BY fullname,gift_id,rec_id

CREATE TABLE `donor_info` (
  `d_id` int(10) NOT NULL auto_increment,
  `active` varchar(10) NOT NULL default '',
  `lastname` varchar(50) NOT NULL default '',
  `firstname` varchar(50) NOT NULL default '',
  `suffix` varchar(50) default NULL,
  `spouse` varchar(30) default NULL,
  `streeth` varchar(60) default NULL,
  `cityh` varchar(50) default NULL,
  `stateh` varchar(10) default NULL,
  `ziph` varchar(20) default NULL,
  `employerp` varchar(50) default NULL,
  `streetp` varchar(60) default NULL,
  `cityp` varchar(50) default NULL,
  `statep` varchar(10) default NULL,
  `zipp` varchar(20) default NULL,
  `employers` varchar(50) default NULL,
  `streets` varchar(60) default NULL,
  `citys` varchar(50) default NULL,
  `states` varchar(10) default NULL,
  `zips` varchar(20) default NULL,
  `homephone` varchar(20) default NULL,
  `cellphp` varchar(20) default NULL,
  `cellphs` varchar(20) default NULL,
  `title` varchar(20) default NULL,
  `emailp` varchar(50) default NULL,
  `emails` varchar(50) default NULL,
  `alumnip` varchar(50) default NULL,
  `alumnis` varchar(50) default NULL,
  `positionp` varchar(50) default NULL,
  `workphonep` varchar(20) default NULL,
  `faxp` varchar(20) default NULL,
  `positions` varchar(50) default NULL,
  `workphones` varchar(20) default NULL,
  `faxs` varchar(20) default NULL,
  `relationship` varchar(50) default NULL,
  `socialsite` varchar(50) default NULL,
  PRIMARY KEY  (`d_id`),
  KEY `active` (`active`,`lastname`,`ziph`,`employerp`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=405 ;


CREATE TABLE `donor_gifts` (
  `gift_id` int(10) NOT NULL auto_increment,
  `d_id` int(10) NOT NULL default '0',
  `ev_id` int(10) NOT NULL default '0',
  `date` date default NULL,
  `goods` varchar(200) default NULL,
  `gift_value` double default NULL,
  `services` varchar(200) default NULL,
  `service_value` varchar(100) default NULL,
  PRIMARY KEY  (`gift_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

CREATE TABLE `donor_receipts` (
  `rec_id` int(10) NOT NULL auto_increment,
  `ev_id` int(10) NOT NULL default '0',
  `d_id` int(10) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `amt` decimal(10,2) NOT NULL default '0.00',
  `type` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`rec_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=597 ;

CREATE TABLE `donor_events` (
  `ev_id` int(10) NOT NULL auto_increment,
  `ev_name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ev_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

Would appreciate your help.

Andy

2

you’ll need two SELECTs, one for the gifts and one for the receipts


  FROM donor_events
INNER
  JOIN donor_gifts 
    ON donor_gifts.ev_id = donor_events.ev_id
INNER
  JOIN donor_info
    ON donor_info.d_id = donor_gifts.d_id
 WHERE donor_events.ev_id = 7


  FROM donor_events
INNER
  JOIN donor_receipts 
    ON donor_receipts.d_id = donor_events.ev_id
INNER
  JOIN donor_info
    ON donor_info.d_id = donor_receipts.d_id
 WHERE donor_events.ev_id = 7

otherwise, if you try to do both in the same SELECT, you’ll get cross join effects

you’ll probably be able to combine the two SELECTs with a UNION

3

Thank you, Rudy.