SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join returning cartesian table. Need single rows

    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
    Code:
    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`)
    	)
    Code:
    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`)
    )
    Code:
    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`)
    )
    Code:
    CREATE TABLE `donor_events` (
    	`ev_id` INT(10) NOT NULL AUTO_INCREMENT,
    	`ev_name` VARCHAR(50) NOT NULL,
    	PRIMARY KEY (`ev_id`)
    )
    SQL
    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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. #3
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 1andyw View Post
    I need a table with one row for each donor_gift and one row for each donor_receipt. Is this possible?
    what you just said sure sounds very much like a UNION
    Code:
    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
    UNION ALL
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •