JQL update needed

Question

I just got put onto a project where one of the requirements have changed and I was wondering if I could simply update the functionality by changing the JQL.

I have the following table in our database:

select * from project_organization;

ID    PROJECT_ID   ORG_ID
1     100          1000
2     100          2000
3     200          1000

Our current JQL gets translated as:

select * from project_organization where org_id=2000

ID    PROJECT_ID   ORG_ID
2     100          2000

I'm trying to write a query that will pull back the following rows:

select po.* from project_organization po 
   LEFT JOIN project_organization po2 
   ON po.org_id=po2.org_id and ??????

ID    PROJECT_ID   ORG_ID
1     100          2000
3     200          null

I've tried a handful of LEFT JOIN queries but so far have been unable to get the results I am looking for.

Can anyone provide any guidance?

Answer 1

SELECT po.id, po.project_id, po2.org_id
FROM `project_organizations` AS po
    LEFT OUTER JOIN `project_organizations` AS po2 ON po.`project_id` = po2.`project_id` AND po2.`org_id` = 2000
    GROUP BY project_id

Based on the previous comments.