QueriesForEndorsements: PVSWiki

The query below shows endorsed candidates given sig_id, officetype_id, and electionyear. For this particular query, we looked at Congressional NRA endorsements in 2012.

- SELECT M.electionyear, M.sig, M.election_candidate_id, M.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, M.state_id, st.name state, M.districtname_id
FROM
(SELECT X.* FROM
(SELECT Z.*
FROM (SELECT A.electionyear, A.election_id, A.sig, A.endorse_id, B.election_candidate_id, B.candidate_id, B.officetype_id, B.office_id, B.state_id, B.districtname_id
FROM (SELECT states_w_nra2012end.* FROM (SELECT e.electionyear, e.state_id elec_state, s.state_id sig_state, s.name sig, endo.endorse_id, endo.sig_id, endo.election_id FROM sig s LEFT JOIN endorse endo USING (sig_id) JOIN election e USING (election_id) WHERE sig_id = 1034 AND e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY elec_state) states_w_nra2012end) A
JOIN (SELECT allC2012_elec_cand.* FROM (SELECT e.officetype_id, e.electionyear, ec.election_candidate_id, ec.election_id, ec.candidate_id, ec.office_id, ec.state_id, ec.districtname_id FROM election e LEFT JOIN election_candidate ec USING (election_id) WHERE e.electionyear = '2012' AND e.officetype_id = 'C' ORDER BY e.officetype_id, ec.state_id, ec.districtname_id) allC2012_elec_cand) B USING (election_id)
WHERE A.elec_state = B.state_id
ORDER BY B.office_id, B.state_id, B.districtname_id) Z
GROUP BY Z.electionyear, Z.election_id, Z.sig, Z.endorse_id, Z.election_candidate_id, Z.candidate_id, Z.officetype_id, Z.office_id, Z.state_id, Z.districtname_id) X
JOIN endorse_candidate end_cand USING (endorse_id) WHERE X.election_candidate_id = end_cand.election_candidate_id) M JOIN candidate c USING (candidate_id) JOIN office o USING (office_id) JOIN state st ON (M.state_id = st.state_id) ORDER BY st.name, o.name DESC, M.districtname_id ;

PVSWiki : QueriesForEndorsements