SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office 
FROM candidate c 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o USING (office_id) 
LEFT JOIN election_candidate ec 
ON ec.candidate_id = c.candidate_id AND ec.office_id = oc.office_id 
LEFT JOIN electionstage_candidate esc USING (election_candidate_id) 
LEFT JOIN election_electionstage ees USING (election_electionstage_id) 
WHERE (esc.electioncandidatestatus_id = 11 AND ees.electionstage_id = 'G') AND (lastelect is null OR firstelect is null OR nextelect is null) 
ORDER BY c.lastname, c.firstname;

SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office 
FROM candidate c 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o USING (office_id) 
JOIN officetype ot USING (officetype_id) 
WHERE oc.officecandidatestatus_id IN (0,2) AND oc.termend is null AND ot.officelevel_id IN ('F', 'S') 
ORDER BY c.lastname, c.firstname

SELECT o.office_id, o.name
FROM office o
LEFT JOIN office_candidate oc USING (office_id)
LEFT JOIN election_candidate ec ON ec.office_id = o.office_id
WHERE oc.office_candidate_id IS NULL AND ec.election_candidate_id IS NULL;

SELECT DISTINCT c.candidate_id, c.lastname, c.firstname, cs.congstatus_id, cong.congress_id, cong.billnumber, cong.state_id
FROM congress cong
JOIN congstatus cs USING (congress_id)
JOIN congstatus_candidate csc USING (congstatus_id)
JOIN office_candidate oc USING (office_candidate_id)
LEFT JOIN candidate c USING (candidate_id)
LEFT JOIN congstatus_candidate csc2 
ON csc2.congstatus_id = csc.congstatus_id AND csc2.congstatus_candidate_id != csc.congstatus_candidate_id
LEFT JOIN office_candidate oc2 
ON oc2.office_candidate_id = csc2.office_candidate_id AND oc2.candidate_id = oc.candidate_id
WHERE oc2.office_candidate_id IS NOT NULL;

SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office 
FROM candidate c 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o USING (office_id) 
LEFT JOIN election_candidate ec 
	ON ec.candidate_id = c.candidate_id AND ec.office_id = oc.office_id 
LEFT JOIN electionstage_candidate esc USING (election_candidate_id) 
LEFT JOIN election_electionstage ees USING (election_electionstage_id) 
WHERE (esc.electioncandidatestatus_id = 11 AND ees.electionstage_id = 'G' AND now() - interval '6 months' > ees.electiondate ) AND (termstart is null AND oc.officecandidatestatus_id = 1) 
ORDER BY c.lastname, c.firstname;

SELECT c.candidate_id, c.firstname, c.lastname 
FROM candidate c 
LEFT JOIN election_candidate ec USING (candidate_id) 
LEFT JOIN office_candidate oc 
	ON oc.candidate_id = c.candidate_id 
WHERE election_candidate_id IS NULL AND oc.office_candidate_id IS NULL;

SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office 
FROM candidate c 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o USING (office_id) 
WHERE substring(oc.nextelect, '\d{4}')::int < extract(year from now()) AND oc.officecandidatestatus_id = 1
ORDER BY c.lastname, c.firstname

SELECT c.candidate_id, c.lastname, c.firstname, ec.state_id, o.name AS office, p.name AS party
FROM election e
JOIN election_candidate ec USING (election_id)
JOIN candidate c USING (candidate_id)
JOIN office o ON (ec.office_id = o.office_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
JOIN electionstage_candidate_party escp USING (electionstage_candidate_id)
JOIN party p USING (party_id)
WHERE c.candidate_id NOT IN 
(SELECT candidate_id FROM 
	(SELECT c.candidate_id, c.lastname, c.firstname, oc.state_id, o.name AS office, p.name AS party
	FROM candidate c 
	JOIN office_candidate oc USING (candidate_id) 
	JOIN office o ON (oc.office_id = o.office_id)
	JOIN office_candidate_party ocp USING (office_candidate_id)
	JOIN party p USING (party_id)
	WHERE o.officetype_id = 'C' AND oc.officecandidatestatus_id = 1) x)
AND o.officetype_id = 'C' AND electionyear = 2014
GROUP BY c.candidate_id, c.lastname, c.firstname, ec.state_id, office, party
ORDER BY ec.state_id, o.name, c.lastname

SELECT oc.office_candidate_id, c.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, oc.state_id, o.name office, oc.districtname_id, dn.name district 
FROM candidate c 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o USING (office_id) 
LEFT JOIN districtname dn USING (districtname_id) 
WHERE oc.officecandidatestatus_id = 1 AND o.officetype_id = 'C' AND oc.state_id NOT IN ('AS', 'DC', 'GU', 'MP', 'PR', 'VI') 
ORDER BY oc.state_id, o.name DESC, oc.districtname_id ;