Practice7Subqueries: PVSWiki

1) Using the fields in the candidate table, we can pull candidate bio information including their names, religion, and professional experience. We can join this table with the office table through the office_candidate junction table, and also join this information with the districtname table for districts, and the finsource_candidate table for CRP ids. Note the use of both JOIN and LEFT JOIN in the query below, think about the repercussions if we did not use LEFT JOIN.

SELECT c.candidate_id, fsc.code crp_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, oc.state_id, dn.name district, c.profession, c.religion FROM office o JOIN office_candidate oc USING (office_id)
JOIN candidate c USING (candidate_id)
LEFT JOIN districtname dn USING (districtname_id)
LEFT JOIN finsource_candidate fsc USING (candidate_id)
WHERE o.officetype_id = 'C' AND oc.officecandidatestatus_id = 1 AND fsc.finsource_id = 1 AND (oc.state_id != 'PR' AND oc.state_id != 'VI' AND oc.state_id != 'GU' AND oc.state_id != 'DC' AND oc.state_id != 'AS')
ORDER BY o.name DESC, oc.state_id, oc.districtname_id

2) For the ratings requested, we can setup 2 subqueries and join them like this, which will return the economic and social policy scores requested.

SELECT A.*, B.sr_econ, B.or_econ

FROM (
SELECT candidate_id, sig_rating sr_social, our_rating or_social
FROM rating_candidate WHERE rating_id = 5948
) A

JOIN (

SELECT candidate_id, sig_rating sr_econ, our_rating or_econ
FROM rating_candidate WHERE rating_id = 5952

) B
USING (candidate_id)

3) The combined query looks something like this. Try to think about why we are using a LEFT JOIN between the cand AND rat sub-queries, and what happens if this join type is not used.

SELECT cand.*, rat.social, rat.econ FROM
(SELECT c.candidate_id, fsc.code crp_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, oc.state_id, dn.name district, c.profession, c.religion FROM office o JOIN office_candidate oc USING (office_id)
JOIN candidate c USING (candidate_id)
LEFT JOIN districtname dn USING (districtname_id)
LEFT JOIN finsource_candidate fsc USING (candidate_id)
WHERE o.officetype_id = 'C' AND oc.officecandidatestatus_id = 1 AND fsc.finsource_id = 1 AND (oc.state_id != 'PR' AND oc.state_id != 'VI' AND oc.state_id != 'GU' AND oc.state_id != 'DC' AND oc.state_id != 'AS')
ORDER BY o.name DESC, oc.state_id, oc.districtname_id) cand
LEFT JOIN
(SELECT A.*, B.econ FROM

(SELECT candidate_id, our_rating social FROM rating_candidate WHERE rating_id = 5948) A

JOIN

(SELECT candidate_id, our_rating econ FROM rating_candidate WHERE rating_id = 5952) B USING (candidate_id)) rat USING (candidate_id) ;

PVSWiki : Practice7Subqueries