Too many rows in return

1andyw

I am running one query and getting cartesian result. I don't know how to structure the query to return the desired result. Desired result being one row of data for each record in the receipts table (10 records) and one row of data for each record in the gifts table (4 records). I should return 14 rows but am returning 40 rows.

donor_info:

CREATE TABLE `donor_info` (
	`d_id` INT(10) NOT NULL AUTO_INCREMENT,
	`active` VARCHAR(10) NOT NULL DEFAULT '',
	`lastname` VARCHAR(50) NOT NULL COLLATE 'latin1_general_cs',
	`firstname` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'latin1_general_cs',
	`suffix` VARCHAR(50) NULL DEFAULT NULL,
	`spouse` VARCHAR(30) NULL DEFAULT NULL,
	`streeth` VARCHAR(60) NULL DEFAULT NULL,
	`cityh` VARCHAR(50) NULL DEFAULT NULL,
	`stateh` VARCHAR(10) NULL DEFAULT NULL,
	`ziph` VARCHAR(20) NULL DEFAULT NULL,
	`employerp` VARCHAR(50) NULL DEFAULT NULL,
	`streetp` VARCHAR(60) NULL DEFAULT NULL,
	`cityp` VARCHAR(50) NULL DEFAULT NULL,
	`statep` VARCHAR(10) NULL DEFAULT NULL,
	`zipp` VARCHAR(20) NULL DEFAULT NULL,
	`employers` VARCHAR(50) NULL DEFAULT NULL,
	`streets` VARCHAR(60) NULL DEFAULT NULL,
	`citys` VARCHAR(50) NULL DEFAULT NULL,
	`states` VARCHAR(10) NULL DEFAULT NULL,
	`zips` VARCHAR(20) NULL DEFAULT NULL,
	`homephone` VARCHAR(20) NULL DEFAULT NULL,
	`cellphp` VARCHAR(20) NULL DEFAULT NULL,
	`cellphs` VARCHAR(20) NULL DEFAULT NULL,
	`title` VARCHAR(20) NULL DEFAULT NULL,
	`emailp` VARCHAR(50) NULL DEFAULT NULL,
	`emails` VARCHAR(50) NULL DEFAULT NULL,
	`alumnip` VARCHAR(50) NULL DEFAULT NULL,
	`alumnis` VARCHAR(50) NULL DEFAULT NULL,
	`positionp` VARCHAR(50) NULL DEFAULT NULL,
	`workphonep` VARCHAR(20) NULL DEFAULT NULL,
	`faxp` VARCHAR(20) NULL DEFAULT NULL,
	`positions` VARCHAR(50) NULL DEFAULT NULL,
	`workphones` VARCHAR(20) NULL DEFAULT NULL,
	`faxs` VARCHAR(20) NULL DEFAULT NULL,
	`relationship` VARCHAR(50) NULL DEFAULT NULL,
	`socialsite` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`d_id`),
	INDEX `active` (`active`, `lastname`, `ziph`, `employerp`)
)

donor_receipts:

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`)
)

donor_gifts:

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,
	`services` VARCHAR(200) NULL DEFAULT NULL,
	`service_value` VARCHAR(100) NULL DEFAULT NULL,
	PRIMARY KEY (`gift_id`)
)

donor_events:

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

query:

SELECT donor_info.lastname,donor_info.firstname,donor_info.d_id,donor_receipts.d_id AS receiptsid,donor_receipts.ev_id AS receiptsevent,donor_receipts.date AS receiptsdate,donor_receipts.amt AS receiptsamt,donor_gifts.d_id AS giftsid,donor_gifts.ev_id,donor_gifts.date,donor_gifts.goods,donor_gifts.gift_value,donor_events.ev_id,donor_events.ev_name 			
FROM donor_info
LEFT JOIN donor_receipts  ON donor_receipts.d_id = donor_info.d_id
LEFT JOIN donor_gifts  ON donor_gifts.d_id = donor_info.d_id 										
JOIN donor_events ON donor_events.ev_id = donor_receipts.ev_id
JOIN donor_events e2 ON e2.ev_id =  donor_gifts.ev_id
WHERE lastname LIKE 'Wheeler' 
AND firstname LIKE 'Rita'

I tried changing order of fields in the ON clauses but that did not change anything.

Thanks,

Andy

johanafm

Well, you are the only one who knows what your identifiers mean. I can only guess. But assuming that each row in donor_receipt should match exactly one row in donor_gift, why are they even in separate tables to begin with? They would both be uniquely identified by the same candidate key. For example, what does ev_id, which exists in both receipts and gifts table, mean?

If these tables do not have a one-to-one correlation, then what you get will be the cartesian product of
- For each donor_info, you will get one row per row in donor_gifts for this donor
- For each donor_info, you will get one row per row in donor_receipts for this donor

1andyw

Thanks for your reply.
Table donor_receipts reflects cash and shows on the books. Table donor_gifts reflects non cash donations.

johanafm;10990572 wrote:
- For each donor_info, you will get one row per row in donor_gifts for this donor
- For each donor_info, you will get one row per row in donor_receipts for this donor

This is exactly what I would like to output.

However, I am getting a repeat of each donor_gift with each donor_receipt. Ten records exist for one client in the receipt table, while four records exist for the same client in the gifts table. My query result returns 40 rows for this client.

I see that by placing a left join with each table, I am asking for each of the donor_info rows to be repeated for each table so joined, so I changed the left join with the donor_gifts table to a right join and the results stayed the same, 40 rows. Changed the right join to an inner join and same result, 40 rows.

My concept is wrong. I just don't see the light at this point.

Andy

Derokorian

1andyw;10990577 wrote:
[...]
This is exactly what I would like to output.

However, I am getting a repeat of each donor_gift with each donor_receipt. Ten records exist for one client in the receipt table, while four records exist for the same client in the gifts table. My query result returns 40 rows for this client.

I see that by placing a left join with each table, I am asking for each of the donor_info rows to be repeated for each table so joined, so I changed the left join with the donor_gifts table to a right join and the results stayed the same, 40 rows. Changed the right join to an inner join and same result, 40 rows.

My concept is wrong. I just don't see the light at this point.

Andy

As per his explaination you would expect 40 rows. You said his explanation is what you want? but his explanation shows you would get 40 rows.
1 donor times 10 receipts times 4 donations = 40 (not 14) because each receipt will show up 4 times (once for each donation) and each donation will show up 10 times (once for each receipt) You could try using the UNION syntax to query the 4 donations and then the 10 receipts....

1andyw

I see what your mean.

I like your Union idea. I will follow up on that. Thanks,

Andy

johanafm

Another way would be to actually put donations and gifts in the same table, since they are essentially the same thing. The table which I now call donation can be given this structure for the stuff given

units, item, type

Where table data from one donor and event might be

500, GBP, cash
1, small table, other

which means that you'd simply join donor and donation

SELECT various, fields
FROM donor
INNER JOIN donation ON donor.id = donation.donor_id
WHERE donor.event_id = @some_event

1andyw

Hi Johanafm,

I need to avoid restructuring the tables at this point if at all possible. I'm trying to keep the cash received and goods received separate because of departmental assignments.

The clients use Excel exclusively. I have to produce an xls document for every report they need. Otherwise, I would have more options.

I'm still working on this. I'm not ready to combine the tables yet. That will be the final desperation.

Thanks a lot for your interest.

By the way, this is volunteer work for a local non profit social service agency.

Andy

johanafm

Well, it's of course all up to you, but in my opinion there is no difference between donating a chair or $200, and you wouldn't have one table for chairs, one for couches and one for cash... Also, what reports you have to produce doesn't matter at all. Just like you can join the results together from two separate queries on two separate tables using union, you can select data twice from one table to produce two reports. Or grab it all from the one table and generate two reports containing different data.

# due to the order by, all cash donations come first in this query
# mainly useful if you want you output two lists on the same web page, with
# cash separate from other stuff.
$query = "SELECT various, stuff FROM donation WHERE event_id = $some_event ORDER BY type=cash DESC";

$result = $mysqli->query($query);
$result = $result->fetchAll();

$cash_report = '';
$other_report = '';

# although, by checking each row for type=='cash', you don't even need the order by
foreach ($result as $row)
{
	if ($row['cash'])
	{
		$cash_report .= create_cash_report_row($row);
	}
	else
	{
		$other_report .= create_non_cash_report($row);
	}
}

Or, if your excel reports are actually CSV files and you are ok with having files generated on the same machine as your database server (or disk network mappable from this machine), you could directly

SELECT various, stuff
INTO OUTFILE '/tmp/cash_donations.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
FROM donation
WHERE event_id = @some_event AND type='cash'


SELECT various, stuff
INTO OUTFILE '/tmp/non_cash_donations.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
FROM donation
WHERE event_id = @some_event AND type='other'

1andyw

I appreciate you input. Thank you.

Andy🙂