Database Query: List of watchers

[Mike]

Is it possible to query a list of all issues and the watchers of each issue within a project?

[Alexey Matveev · Accepted solution]

Hello,

It would be like this:

SELECT *
  FROM jiraissue i, userassociation a, project p 
  WHERE ASSOCIATION_TYPE = 'WatchIssue'
  and i.ID = a.SINK_NODE_ID
  and i.project = p.id
  and p.pley = 'youkey'

[Mike]

I am getting: 

Msg 207, Level 16, State 1, Line 6

Invalid column name 'pley'.

[Alexey Matveev]

SELECT *
  FROM jiraissue i, userassociation a, project p 
  WHERE ASSOCIATION_TYPE = 'WatchIssue'
  and i.ID = a.SINK_NODE_ID
  and i.project = p.id
  and p.pkey = 'youkey'

[Gregory Kneller]

SELECT u.lower_username, concat(p.pkey,'-',issuenum) 

FROM jiraissue i, userassociation a, app_user u, project p 

where association_type='WatchIssue' 

     and i.id=sink_node_id 

     and i.project=p.id

     and  a.source_name=u.user_key

[Elizabeth Ramsey]

This query was perfect for what I need... you just need to change u.lower_username to u.lower_user_name.  Thanks! =)