sql query to get project list from database

[HaRsHaS' V]

Hey guys

I am looking to get list of jira projects and the associated agile boards from the database

I would need a sql query as I have the access to database.

[Nic Brough -Adaptavist- · Accepted solution]

Don't bother with SQL, it's the worst possible way to begin reporting on Jira systems.

You're going to struggle with this question because:

• Boards have nothing to do with projects
• So you'll need to look at the queries the boards run
• Which are stored in a format you will need to parse for "... and project = x ..." or " ... and project in ("x", "y", "z") if you want to associate them directly with projects
• And worry about queries that are not tied to projects (3/4 of the boards I use regularly have no project clause - in our system, you would have to assume "most of Nic's boards cover every project"

Jira's searches and REST API can make this very easy for you.  Don't try it in SQL, you won't like it.

[HaRsHaS' V]

@Nic Brough -Adaptavist-

thanks for the reply. totally agree with you. boards are connected to filters and it to find write one. However, I did find one community source.

https://community.atlassian.com/t5/Answers-Developer-Questions/Database-schema-relationship-between-a-board-and-a-project/qaq-p/481907

However, the code returns and error 

" subquery returned more than 1 value" in line 1. 

I tried to add top or limit command to select statement and it goes nowhere.

Could you please check and run this code and let me know whats wrong?

[HaRsHaS' V]

@Nic Brough -Adaptavist- 

I think I got this resolved. it took some time but yes.