@@=====**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
%%