@@=====**Tags Queries**=====@@

[[http://wiki.votesmart.org/CategoryResearch <-- Back to the Research Guide]]
[[http://wiki.votesmart.org/TagsManagement <-- Back to the Tags Management Guide]]

>>Queries
[[QueriesForSpeeches Speeches Queries]]>>**__Queries to See What Pieces of Data a Tag Has Been Used On:__**

__//Public Statements//__
%%(language-ref)
SELECT c.candidate_id, s.title, t.tag_id, s.speechdate, s.speech_id
FROM speech s JOIN speech_tag t USING (speech_id) JOIN speech_candidate c USING (speech_id)
WHERE t.tag_id = '1046'
ORDER BY c.candidate_id, s.title, t.tag_id, s.speechdate, s.speech_id
%%

__//Profiles - Experience//__
%%(language-ref)
SELECT t.tag_id, t.name, c.candidate_id, c.firstname, c.lastname
FROM tag t JOIN experience_tag ex USING (tag_id) JOIN experience e USING (experience_id) JOIN candidate c USING (candidate_id)
WHERE t.tag_id = '104316'
GROUP BY t.tag_id, t.name, c.candidate_id
ORDER BY c.lastname, c.firstname
%%

__//Profiles - Education//__
%%(language-ref)
SELECT t.tag_id, t.name, c.candidate_id, c.firstname, c.lastname
FROM tag t JOIN education_tag e USING (tag_id) JOIN education ee USING (education_id) JOIN candidate c USING (candidate_id)
WHERE t.tag_id = '41210'
GROUP BY t.tag_id, t.name, c.candidate_id
ORDER BY c.lastname, c.firstname
%%

__//Profiles - Additional Bio//__
%%(language-ref)
SELECT t.tag_id, t.name, c.candidate_id, c.lastname, c.firstname
FROM tag t JOIN additional_candidate_tag act USING (tag_id)
JOIN additional_candidate ac USING (additional_candidate_id)
JOIN candidate c USING (candidate_id)
WHERE t.tag_id = '104316'
%%

__//Political Courage Test//__
%%(language-ref)
SELECT t.tag_id, t.name, c.candidate_id, c.firstname, c.lastname, nc.receiveddate
FROM tag t JOIN npatanswer_tag nt USING (tag_id)
JOIN npatanswer n USING (npatanswer_id)
JOIN npatcandidate nc USING (npatcandidate_id)
JOIN election_candidate ec USING (election_candidate_id)
JOIN candidate c USING (candidate_id)
WHERE t.tag_id = '95461'
%%

__//Key Votes//__
%%(language-ref)
SELECT t.tag_id, t.name, c.candidate_id
FROM tag t JOIN congcategory_tag cct USING (tag_id)
JOIN congstatus cs USING (congcategory_id)
JOIN congstatus_candidate csc USING (congstatus_id)
JOIN office_candidate oc USING (office_candidate_id)
JOIN candidate c USING (candidate_id)
WHERE t.tag_id = '20353'
%%


__//Key Votes - Displays Synopsis of Bill That Tag was Used On//__
%%(language-ref)
SELECT t.tag_id, t.name, cos.synopsis
FROM tag t JOIN congcategory_tag cct USING (tag_id)
JOIN congstatus cs USING (congcategory_id)
JOIN congsynopsis cos USING (congsynopsis_id)
WHERE t.tag_id = '20353'
%%

**__All Tags Queries__**
__//First One//__

__//Second One - Includes Number of Times Used//__
%%(language-ref)
SELECT t.tag_id
,
t.name
,
at.sect
,
count
(t.tag_id) AS count
FROM (
(
SELECT ac.tag_id
,
'biotext'::text AS sect
FROM (
SELECT biotext_tag.tag_id
FROM biotext_tag
UNION ALL
SELECT additional_tag.tag_id
FROM additional_tag
UNION ALL
SELECT additional_candidate_tag.tag_id
FROM additional_candidate_tag
UNION ALL
SELECT education_tag.tag_id
FROM education_tag
UNION ALL
SELECT religion_tag.tag_id
FROM religion_tag
UNION ALL
SELECT experience_tag.tag_id
FROM experience_tag
) ac
UNION ALL
SELECT committee_tag.tag_id
,
'committee'::text AS sect
FROM committee_tag
UNION ALL
SELECT congcategory_tag.tag_id
,
'cong'::text AS sect
FROM congcategory_tag
UNION ALL
SELECT measure_tag.tag_id
,
'measure'::text AS sect
FROM measure_tag
UNION ALL
SELECT npatanswer_tag.tag_id
,
'npatanswer'::text AS sect
FROM npatanswer_tag
UNION ALL
SELECT rating_tag.tag_id
,
'rating'::text AS sect
FROM rating_tag
UNION ALL
SELECT sig_tag.tag_id
,
'sig'::text AS sect
FROM sig_tag
UNION ALL
SELECT speech_tag.tag_id
,
'speech'::text AS sect
FROM speech_tag
UNION ALL
SELECT party_tag.tag_id
,
'party'::text AS sect
FROM party_tag
) at
JOIN tag t
USING (tag_id)
)
GROUP BY t.tag_id
, t.name
, at.sect
ORDER BY count
(t.tag_id);
%%

**__A Query That Displays All Modified Tags__**
%%(language-ref)
Select *
FROM tag
WHERE modified is NOT NULL
ORDER BY tag_id desc
%%