Simplification of SQL Query

SQL is not my specialty. I have a working query that accomplishes what I need it to, but I know there has got to be a cleaner way of accomplishing my goal.

The query should return:

 - Name
 - Date of most recent donation (if any)
 - Political Party guess as: R, D, or U, where:
    - R = Likely Republican (more republican donations than other donations)
    - D = Likely Democrat (more democrat donations than other donations)
    - U = Undtermined (no donations on record, or R and D are tied for first place.
- Address

Here is a SQLFiddle link to where you can find the tables and my solution: http://sqlfiddle.com/#!9/f2303f/2. Alternatively, here is the schema build:

CREATE TABLE People (
ID INT UNSIGNED  DEFAULT '0000' NOT NULL,
Name  CHAR(20)      DEFAULT ''     NOT NULL,
Address   CHAR(40)      DEFAULT ''     NOT NULL,
PRIMARY KEY(ID));

INSERT INTO People VALUES
(1, "Name1", "Idaho"),
(2, "Name2","UCLA"),
(3, "Name3", "Carolina"),
(4, "Name4", "Portland");

CREATE TABLE Donations (
ID INT UNSIGNED  DEFAULT '0000' NOT NULL,
People_ID  INT UNSIGNED  DEFAULT '0000' NOT NULL,
Donation_Date   DATE  NOT NULL,
Party   INT SIGNED  DEFAULT '0000' NOT NULL,
PRIMARY KEY(ID));

INSERT INTO Donations VALUES
#Name1
(1, 1, "2000-06-23", 1), (2, 1, "2000-06-24",-1),

#Name2
(3, 2, "2001-06-25", 1),(4, 2, "2001-06-26", 1),

# Name3
(5, 3, "2002-06-26", -1),(6, 3, "2002-06-27", -1);

#Name4
#None

And here is the query:

SELECT Name, 
  IFNULL(donation_date, 'None') as 'Recent Donation', 
  IFNULL(voting_guess, "U") as 'Party Guess', 
  Address
FROM people p

LEFT JOIN donations on donations.people_id = p.id AND donations.donation_date = (
  SELECT MAX(donation_date) 
  FROM donations
  WHERE donations.people_id = p.id)
  
LEFT JOIN 
  (SELECT people_id, 
    (CASE
       WHEN SUM(party) > 0 THEN "R"
       WHEN SUM(party) < 0 THEN "D"
     END
    ) AS voting_guess
    FROM donations
    GROUP BY people_id
  ) voting ON P.id = voting.people_id

Specifically, I’d like to try and condense the latter two left joins into one. Any advice?

Answer

You can left join a single derived table in which the aggregation is done.

SELECT p1.name,
       coalesce(x1.donation_date, 'None') `Recent Donation`,
       coalesce(x1.voting_guess, 'U') `Party Guess`,
       p1.address
       FROM people p1
            LEFT JOIN (SELECT d1.people_id,
                              max(d1.donation_date) donation_date,
                              CASE
                                WHEN sum(d1.party) > 0 THEN
                                  'R'
                                WHEN sum(d1.party) < 0 THEN
                                  'D'
                              END voting_guess
                              FROM donations d1
                              GROUP BY d1.people_id) x1
                      ON x1.people_id = p1.id;

And you should also learn to use proper quotes as near as possible to standard SQL to minimize compatibility problems when porting to another DBMS. For literals only use single quotes and only use single quotes for literals. For identifiers only use back ticks and only use back ticks for identifiers. Do not use double quotes at all. In standard SQL and therefore many other DBMS double quotes are for identifiers, like back ticks in MySQL.