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?
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.