Wiki source for QueriesForPastOfficials
====Queries for Former Office Holders====
- SELECT c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'MS' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'G' ORDER BY vec.state_id, vec.office, vec.districtname_id
**This query displays a list of candidates that were elected in a specific state in a specific year (in this case, in Mississippi in 2007). This is useful when you want to display all legislators that were "active" in a state in a given year in the past.**
- SELECT a.firstname, a.lastname, a.state_id, a.office, a.districtname, a.gender, a.birthdate FROM (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.districtname_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'P') a LEFT OUTER JOIN (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.electioncandidatestatus_id, vec.electionstage_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id != 11 AND vec.electionstage_id = 'G') b USING (candidate_id) WHERE b is null ORDER BY a.office, a.districtname_id
**This query allows us to exclude a set of candidates that won Primary elections but lost their General elections from a list that displays all candidates that were elected to office in a given year. This query is useful in cases like Louisiana (displayed here), where candidates receiving greater than 50 percent of the vote in a so-called "Jungle Primary" are automatically elected and do not need to advance to a general election contest. Therefore, to find all legislators serving in Louisiana in a particular year in the past, you need to identify all those candidates who won primaries and did have a general election and those candidates that won general election contests. It uses the "LEFT OUTER JOIN" command and then specifies "WHERE b is null."**
Back to [[UsefulResearchQueries Useful Research Queries]].
- SELECT c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'MS' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'G' ORDER BY vec.state_id, vec.office, vec.districtname_id
**This query displays a list of candidates that were elected in a specific state in a specific year (in this case, in Mississippi in 2007). This is useful when you want to display all legislators that were "active" in a state in a given year in the past.**
- SELECT a.firstname, a.lastname, a.state_id, a.office, a.districtname, a.gender, a.birthdate FROM (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.districtname_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id = 11 AND vec.electionstage_id = 'P') a LEFT OUTER JOIN (SELECT c.candidate_id, c.firstname, c.lastname, vec.state_id, vec.office, vec.districtname, c.gender, c.birthdate, vec.electioncandidatestatus_id, vec.electionstage_id FROM candidate c JOIN v_election_candidate vec USING (candidate_id) WHERE vec.state_id = 'LA' AND vec.officetype_id = 'L' AND vec.electionyear = '2007' AND vec.electioncandidatestatus_id != 11 AND vec.electionstage_id = 'G') b USING (candidate_id) WHERE b is null ORDER BY a.office, a.districtname_id
**This query allows us to exclude a set of candidates that won Primary elections but lost their General elections from a list that displays all candidates that were elected to office in a given year. This query is useful in cases like Louisiana (displayed here), where candidates receiving greater than 50 percent of the vote in a so-called "Jungle Primary" are automatically elected and do not need to advance to a general election contest. Therefore, to find all legislators serving in Louisiana in a particular year in the past, you need to identify all those candidates who won primaries and did have a general election and those candidates that won general election contests. It uses the "LEFT OUTER JOIN" command and then specifies "WHERE b is null."**
Back to [[UsefulResearchQueries Useful Research Queries]].
