Tags Queries: PVSWiki

Tags Queries

<-- Back to the Research Guide
<-- Back to the Tags Management Guide

Queries to See What Pieces of Data a Tag Has Been Used On:

Public Statements

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

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

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

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

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

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

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

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

Select *
FROM tag
WHERE modified is NOT NULL
ORDER BY tag_id desc

PVSWiki : TagsQueries

Tags Queries