Join returning cartesian table. Need single rows

Databases
1

My sql returns a cartesian table with too many rows. I need a table with one row for each donor_gift and one row for each donor_receipt. Is this possible?

Andy

CREATE TABLE `donor_info` (
	`d_id` INT(10) NOT NULL AUTO_INCREMENT,
	`lastname` VARCHAR(50) NOT NULL,
	`firstname` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`d_id`)
	)


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 NULL DEFAULT NULL,
	`goods` VARCHAR(200) NULL DEFAULT NULL,
	`gift_value` DOUBLE NULL DEFAULT NULL,
	PRIMARY KEY (`gift_id`)
)


CREATE TABLE `donor_receipts` (
	`rec_id` INT(10) NOT NULL AUTO_INCREMENT,
	`ev_id` INT(10) NOT NULL,
	`d_id` INT(10) NOT NULL,
	`date` DATE NOT NULL,
	`amt` DECIMAL(10,2) NOT NULL,
	`type` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`rec_id`)
)



CREATE TABLE `donor_events` (
	`ev_id` INT(10) NOT NULL AUTO_INCREMENT,
	`ev_name` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`ev_id`)
)

SQL

SELECT donor_info.lastname,donor_info.firstname,donor_info.employerp,donor_info.emailp,donor_info.d_id,donor_receipts.d_id,donor_receipts.ev_id,donor_receipts.date AS Cashdate,donor_receipts.amt AS Cashamt,donor_gifts.d_id,donor_gifts.ev_id,donor_gifts.date AS Giftdate,donor_gifts.goods AS Gift,donor_gifts.gift_value AS Giftvalue,donor_events.ev_id,donor_events.ev_name 			
FROM donor_info
LEFT JOIN donor_receipts  ON donor_info.d_id = donor_receipts.d_id
LEFT JOIN donor_gifts  ON donor_info.d_id = donor_receipts.d_id										
LEFT JOIN donor_events ON ((donor_receipts.ev_id = donor_events.ev_id)||(donor_gifts.ev_id=donor_events.ev_id))
2 
LEFT JOIN donor_gifts  ON donor_info.d_id = donor_receipts.d_id

I don’t quite understand the relations between your tables, but this join is obviously wrong. You’re not joining on any column from the table donor_gifts, so this would give a cartesian table.

3

Thanks for catching my error.
I have a table of donors, a table of cash receipts, a table of gifts(goods), and a table of events. A donor I use for a test has 10 rows in the cash table, 4 rows in the gifts table. I would hope for a return of 14 rows; one for each contribution. The sql returns 76 rows. I’ve tried switching joins from left to right, to inner. Nothing worked.

SELECT donor_info.lastname,donor_info.firstname,donor_info.employerp,donor_info.emailp,donor_info.d_id,donor_receipts.rec_id,donor_receipts.ev_id,donor_receipts.date AS Cashdate,donor_receipts.amt AS Cashamt,donor_gifts.gift_id,donor_gifts.ev_id,donor_gifts.date AS Giftdate,donor_gifts.goods AS Gift,donor_gifts.gift_value AS Giftvalue,donor_events.ev_id,donor_events.ev_name 			
FROM donor_info
LEFT JOIN donor_receipts  ON donor_info.d_id = donor_receipts.d_id
LEFT JOIN donor_gifts  ON donor_info.d_id = donor_gifts.d_id										
LEFT JOIN donor_events ON ((donor_receipts.ev_id = donor_events.ev_id)||(donor_gifts.ev_id=donor_events.ev_id))

Thanks,

Andy

4

what you just said sure sounds very much like a UNION


SELECT donor_info.d_id
     , donor_info.lastname
     , donor_info.firstname
     , donor_info.employerp
     , donor_info.emailp
     , donor_events.ev_id
     , donor_events.ev_name
     , 'receipt'           AS activity 			
     , donor_receipts.date AS activity_date
     , donor_receipts.amt  AS activity_amt
     , donor_receipts.type AS activity_extrainfo
  FROM donor_receipts 
INNER
  JOIN donor_events
    ON donor_events.ev_id = donor_receipts.ev_id
INNER
  JOIN donor_info
    ON donor_info.d_id = donor_receipts.d_id
[COLOR="#0000FF"]UNION ALL[/COLOR]
SELECT donor_info.d_id
     , donor_info.lastname
     , donor_info.firstname
     , donor_info.employerp
     , donor_info.emailp
     , donor_events.ev_id
     , donor_events.ev_name 			
     , 'gift'                  AS activity 
     , donor_gifts.date        AS activity_date
     , donor_gifts.gift_value  AS activity_amt 
     , donor_gifts.goods       AS activity_extrainfo
  FROM donor_gifts  
INNER
  JOIN donor_events
    ON donor_events.ev_id = donor_gifts.ev_id
INNER
  JOIN donor_info
    ON donor_info.d_id = donor_gifts.d_id
5

That nailed it, Rudy. Pure art.
I had tried union all myself but didn’t recognize the necessity of joining the donor_events to each part of the union.
Thanks, again.

Andy