Revision history for ProfilesErrorQueries
Revision [57339]
Last edited on 2020-12-21 17:44:07 by JamesW [Adjustments made for 2020 research updates]Additions:
//**Candidates Running in the General who were not in the Primary:** - Returns a list of candidates who advanced from the primary elections (this query is useful following primary election candidate research to narrow down tracking sheets)://
(SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id
FROM election e
JOIN election_electionstage ee ON e.election_id = ee.election_id
JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id
JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id
JOIN candidate c ON ec.candidate_id = c.candidate_id
JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id
JOIN party p ON ecp.party_id = p.party_id
WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'G' AND electioncandidatestatus_id = 8 AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019'
Order BY ec.districtname_id, c.lastname)
EXCEPT
(SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id
FROM election e
JOIN election_electionstage ee ON e.election_id = ee.election_id
JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id
JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id
JOIN candidate c ON ec.candidate_id = c.candidate_id
JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id
JOIN party p ON ecp.party_id = p.party_id
WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'P' AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019'
Order BY ec.districtname_id, c.lastname)
//**'Present' capitalized in span**://
SELECT distinct c.candidate_id, firstname, lastname
JOIN experience e using (candidate_id)
JOIN office_candidate using (candidate_id)
WHERE span in ('Present', '%-Present') and officecandidatestatus_id = '1'
ORDER BY c.candidate_id
//**Educational experiences that don't have a school**://
SELECT DISTINCT (c.candidate_id), o.name, c.lastname, c.firstname, c.state_id, e.degree, e.school
JOIN education e ON c.candidate_id = e.candidate_id
JOIN office_candidate oc ON c.candidate_id = oc.candidate_id
JOIN office o ON oc.office_id = o.office_id
WHERE o.officetype_id = 'L' AND e.degree IS NOT NULL AND e.school IS NULL
ORDER BY o.name, c.state_id, c.lastname ASC
//**officecandidatestatus is elected**://
SELECT c.candidate_id, c.firstname, c.lastname
JOIN office_candidate oc USING (candidate_id)
WHERE officecandidatestatus_id = '3'
ORDER BY c.candidate_id
//**Span contains spaces**://
SELECT c.candidate_id, firstname, lastname
JOIN experience e USING (candidate_id)
WHERE span LIKE '% - %'
//**Title field has a gendered title**://
SELECT c.candidate_id, c.firstname, c.lastname
JOIN experience e USING (candidate_id)
WHERE e.title LIKE '%man%' AND candidatestatus_id = '1'
(SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id
FROM election e
JOIN election_electionstage ee ON e.election_id = ee.election_id
JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id
JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id
JOIN candidate c ON ec.candidate_id = c.candidate_id
JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id
JOIN party p ON ecp.party_id = p.party_id
WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'G' AND electioncandidatestatus_id = 8 AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019'
Order BY ec.districtname_id, c.lastname)
EXCEPT
(SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id
FROM election e
JOIN election_electionstage ee ON e.election_id = ee.election_id
JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id
JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id
JOIN candidate c ON ec.candidate_id = c.candidate_id
JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id
JOIN party p ON ecp.party_id = p.party_id
WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'P' AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019'
Order BY ec.districtname_id, c.lastname)
//**'Present' capitalized in span**://
SELECT distinct c.candidate_id, firstname, lastname
JOIN experience e using (candidate_id)
JOIN office_candidate using (candidate_id)
WHERE span in ('Present', '%-Present') and officecandidatestatus_id = '1'
ORDER BY c.candidate_id
//**Educational experiences that don't have a school**://
SELECT DISTINCT (c.candidate_id), o.name, c.lastname, c.firstname, c.state_id, e.degree, e.school
JOIN education e ON c.candidate_id = e.candidate_id
JOIN office_candidate oc ON c.candidate_id = oc.candidate_id
JOIN office o ON oc.office_id = o.office_id
WHERE o.officetype_id = 'L' AND e.degree IS NOT NULL AND e.school IS NULL
ORDER BY o.name, c.state_id, c.lastname ASC
//**officecandidatestatus is elected**://
SELECT c.candidate_id, c.firstname, c.lastname
JOIN office_candidate oc USING (candidate_id)
WHERE officecandidatestatus_id = '3'
ORDER BY c.candidate_id
//**Span contains spaces**://
SELECT c.candidate_id, firstname, lastname
JOIN experience e USING (candidate_id)
WHERE span LIKE '% - %'
//**Title field has a gendered title**://
SELECT c.candidate_id, c.firstname, c.lastname
JOIN experience e USING (candidate_id)
WHERE e.title LIKE '%man%' AND candidatestatus_id = '1'
Deletions:
SELECT c.candidate_id, c.lastname, s.title, s.speechdate
JOIN speech_candidate sc USING (candidate_id)
JOIN speech s USING (speech_id)
WHERE s.speechtype_id = '7' AND s.title NOT LIKE '%Issue Position%' AND s.speechdate > '%12/31/2013%'
ORDER BY candidate_id %%
Revision [18720]
Edited on 2016-11-23 12:17:03 by jamiem [Adjustments made for 2020 research updates]Additions:
//**"Present" Candidate Span** - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections://
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections://
//**Committee Membership** - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab://
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections://
//**Committee Membership** - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab://
Deletions:
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections:
//**Committee Membership** - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab:
Revision [18719]
Edited on 2016-11-23 12:15:09 by jamiem [Adjustments made for 2020 research updates]Additions:
//**Committee Membership** - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab:
Deletions:
Revision [18718]
Edited on 2016-11-23 12:14:33 by jamiem [Adjustments made for 2020 research updates]Additions:
//**Committee Membership** - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab.
SELECT c.candidate_id, com.name, com.state_id, com.committeetype_id
FROM committee com
JOIN committee_candidate cc ON (cc.committee_id = com.committee_id)
JOIN office_candidate oc ON (oc.office_candidate_id = cc.office_candidate_id)
JOIN candidate c ON (c.candidate_id = oc.candidate_id)
WHERE oc.officecandidatestatus_id = '0'
GROUP by com.state_id, c.candidate_id, com.name, com.committeetype_id
ORDER by com.state_id
SELECT c.candidate_id, com.name, com.state_id, com.committeetype_id
FROM committee com
JOIN committee_candidate cc ON (cc.committee_id = com.committee_id)
JOIN office_candidate oc ON (oc.office_candidate_id = cc.office_candidate_id)
JOIN candidate c ON (c.candidate_id = oc.candidate_id)
WHERE oc.officecandidatestatus_id = '0'
GROUP by com.state_id, c.candidate_id, com.name, com.committeetype_id
ORDER by com.state_id
Revision [18717]
Edited on 2016-11-23 11:18:22 by jamiem [Adjustments made for 2020 research updates]Additions:
//**"Present" Candidate Span** - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections:
Deletions:
Revision [18643]
Edited on 2016-11-17 15:53:01 by jamiem [Adjustments made for 2020 research updates]Additions:
//**Office Experience** - This query Finds any candidate in the 2016 election that no longer holds a Congressional or State Legislative office but have "present" in their office experience entry. Written for the 2016 election, alter el.election year for future elections or remove completely to find all instances in our database://
Deletions:
Revision [18593]
Edited on 2016-11-14 10:12:21 by jamiem [Adjustments made for 2020 research updates]Additions:
//**Candidate Span** - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections:
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections:
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections:
Deletions:
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections.
Revision [18522]
Edited on 2016-10-19 14:17:39 by jamiem [Adjustments made for 2020 research updates]Additions:
//**Office Experience** - This query Finds any candidate in the 2016 election that no longer holds political office but have "present" in their office experience entry. Written for the 2016 election, alter el.election year for future elections://
SELECT oc.candidate_id, e.title, e.organization, e.span, oc.officecandidatestatus_id
FROM office_candidate oc
JOIN experience e ON (e.candidate_id = oc.candidate_id)
JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id)
JOIN election el ON (el.election_id = ec.election_id)
JOIN officecandidatestatus ocs ON (ocs.officecandidatestatus_id = oc.officecandidatestatus_id)
WHERE experience_id NOT IN (SELECT e.experience_id FROM experience e JOIN office_candidate oc ON (oc.candidate_id = e.candidate_id) WHERE oc.officecandidatestatus_id = 1) AND (e.title iLIKE '%representative%' OR e.title iLIKE '%Senator%' OR e.title iLIKE '%Assembly%' OR e.title iLIKE '%Minority%' or e.title iLIKE '%speaker%' or e.title iLIKE '%majority%') AND e.span iLIKE '%present%' AND el.electionyear = 2016 AND e.experiencetype_id iLIKE 'P'
ORDER by candidate_id, e.organization, e.title, e.span
%%
//**Missing Cats and Tags** - Lists the candidates with at least 5 entries lacking BOTH cats and tags. Written for the 2016 election, alter el.election year for future elections://
SELECT count (e.candidate_id), e.candidate_id
FROM experience e
JOIN election_candidate ecc ON (ecc.candidate_id = e.candidate_id)
JOIN election el ON (el.election_id = ecc.election_id)
WHERE experience_id NOT IN (SELECT ec.experience_id FROM experience_category ec) AND experience_id NOT IN (SELECT et.experience_id FROM experience_tag et) AND el.electionyear = 2016
GROUP by e.candidate_id
HAVING COUNT (e.candidate_id) >5
ORDER BY COUNT (e.candidate_id) desc
%%
//**Candidate Span** - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections.
SELECT c.candidate_id, ex.title, ex.organization, ex.span
FROM experience ex
JOIN candidate c On (c.candidate_id = ex.candidate_id)
JOIN election_candidate ec ON (ec.candidate_id = c.candidate_id)
JOIN election el USING (election_id)
WHERE experiencetype_id = 'P' AND ex.title iLIKE 'candidate' AND ex.span iLike '%present%'
ORDER BY candidate_id
%%
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections.
SELECT oc.candidate_id, o.name, ec.state_id, o.title, oc.firstelect, dn.name AS district
FROM office_candidate oc
JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id)
JOIN election el ON (el.election_id = ec.election_id)
JOIN office o ON (o.office_id = oc.office_id)
JOIN districtname dn ON (dn.districtname_id = oc.districtname_id)
WHERE oc.candidate_id NOT IN (SELECT candidate_id FROM experience WHERE title SIMILAR TO '%(Representative|Senator|Delegate|Assembly Member%)' AND experiencetype_id = 'P') AND oc.officecandidatestatus_id = 1 AND o.officetype_id SIMILAR TO '%(C|L)%' AND el.electionyear = 2016
ORDER BY oc.state_id, oc.candidate_id, o.name
%%
SELECT oc.candidate_id, e.title, e.organization, e.span, oc.officecandidatestatus_id
FROM office_candidate oc
JOIN experience e ON (e.candidate_id = oc.candidate_id)
JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id)
JOIN election el ON (el.election_id = ec.election_id)
JOIN officecandidatestatus ocs ON (ocs.officecandidatestatus_id = oc.officecandidatestatus_id)
WHERE experience_id NOT IN (SELECT e.experience_id FROM experience e JOIN office_candidate oc ON (oc.candidate_id = e.candidate_id) WHERE oc.officecandidatestatus_id = 1) AND (e.title iLIKE '%representative%' OR e.title iLIKE '%Senator%' OR e.title iLIKE '%Assembly%' OR e.title iLIKE '%Minority%' or e.title iLIKE '%speaker%' or e.title iLIKE '%majority%') AND e.span iLIKE '%present%' AND el.electionyear = 2016 AND e.experiencetype_id iLIKE 'P'
ORDER by candidate_id, e.organization, e.title, e.span
%%
//**Missing Cats and Tags** - Lists the candidates with at least 5 entries lacking BOTH cats and tags. Written for the 2016 election, alter el.election year for future elections://
SELECT count (e.candidate_id), e.candidate_id
FROM experience e
JOIN election_candidate ecc ON (ecc.candidate_id = e.candidate_id)
JOIN election el ON (el.election_id = ecc.election_id)
WHERE experience_id NOT IN (SELECT ec.experience_id FROM experience_category ec) AND experience_id NOT IN (SELECT et.experience_id FROM experience_tag et) AND el.electionyear = 2016
GROUP by e.candidate_id
HAVING COUNT (e.candidate_id) >5
ORDER BY COUNT (e.candidate_id) desc
%%
//**Candidate Span** - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections.
SELECT c.candidate_id, ex.title, ex.organization, ex.span
FROM experience ex
JOIN candidate c On (c.candidate_id = ex.candidate_id)
JOIN election_candidate ec ON (ec.candidate_id = c.candidate_id)
JOIN election el USING (election_id)
WHERE experiencetype_id = 'P' AND ex.title iLIKE 'candidate' AND ex.span iLike '%present%'
ORDER BY candidate_id
%%
//**Incumbent Political Experience** - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections.
SELECT oc.candidate_id, o.name, ec.state_id, o.title, oc.firstelect, dn.name AS district
FROM office_candidate oc
JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id)
JOIN election el ON (el.election_id = ec.election_id)
JOIN office o ON (o.office_id = oc.office_id)
JOIN districtname dn ON (dn.districtname_id = oc.districtname_id)
WHERE oc.candidate_id NOT IN (SELECT candidate_id FROM experience WHERE title SIMILAR TO '%(Representative|Senator|Delegate|Assembly Member%)' AND experiencetype_id = 'P') AND oc.officecandidatestatus_id = 1 AND o.officetype_id SIMILAR TO '%(C|L)%' AND el.electionyear = 2016
ORDER BY oc.state_id, oc.candidate_id, o.name
%%
Additions:
[[QueriesForOfficials Queries for Officials]]
[[CheckingWorkProfiles Checking Work for Profiles]]
//**Issue Positions** - This query finds all speeches that have been marked as speechtype 7 with Issue Position either spelled wrong or missing since December 31, 2013. This query often times shows real speeches that were accidentally marked as Issue Positions. Send this list to the head of the speeches department so that they can fix them://
[[CheckingWorkProfiles Checking Work for Profiles]]
//**Issue Positions** - This query finds all speeches that have been marked as speechtype 7 with Issue Position either spelled wrong or missing since December 31, 2013. This query often times shows real speeches that were accidentally marked as Issue Positions. Send this list to the head of the speeches department so that they can fix them://
Deletions:
//**Issue Positions** - This query finds all speeches that have been marked as speechtype 7 with Issue Position either spelled wrong or missing. This query often times shows real speeches that were accidentally marked as Issue Positions. Send this list to the head of the speeches department so that they can fix them://
Additions:
//**Education** - This query shows all degrees listed with periods in them, which is a big no-no://
SELECT candidate_id, degree
FROM education
WHERE degree LIKE '%B.A%' OR degree LIKE '%M.A%' OR degree LIKE '%B.S.%' OR degree LIKE '%M.S%'%%
SELECT candidate_id, degree
FROM education
WHERE degree LIKE '%B.A%' OR degree LIKE '%M.A%' OR degree LIKE '%B.S.%' OR degree LIKE '%M.S%'%%
