MySQL Forums
Forum List  »  Newbie

Joining different rows on same table with different critera
Posted by: Waqas Khan
Date: October 06, 2015 03:45AM

Hi,

I tried asking around and googling and I have ended up with a query that kills the DB in every possible sense.

I have 3 tables:

[jiraissue] :- this keeps information about jira tickets (who its assigned to)

[changegroup] :- this keeps change date, the id of the ticket being changed and an id to the change details

[changeitem] :- this contains the change id, the change field and new/old values


I want the following output:

[time_taken_to_resolve],[assigned_date],[resolve_date],[jira_ticket_details_from_jiraissue]

Criteria is jiraissue.ASSIGNEE value.

I came up with the following:

SELECT TIMESTAMPDIFF(HOUR,a.assigned_date,b.resolved_date),a.*, b.*
FROM jiraissue
INNER JOIN (
SELECT changegroup.CREATED as "assigned_date",changegroup.issueid as jid
FROM changegroup
JOIN changeitem ON changegroup.ID = changeitem.groupid
WHERE changeitem.FIELD="assignee" #AND changegroup.issueid=72426 #jiraissue.ASSIGNEE="waqask";
GROUP BY changegroup.issueid
ORDER BY changegroup.CREATED DESC
) as a ON a.jid = jiraissue.id
INNER JOIN (
SELECT changegroup.CREATED as "resolved_date", changegroup.issueid as jid
FROM changegroup
JOIN jiraissue on changegroup.issueid = jiraissue.ID
JOIN changeitem ON changegroup.ID = changeitem.groupid
WHERE changeitem.FIELD="status" AND changeitem.NEWVALUE=5 #AND jiraissue.id=72426 #jiraissue.ASSIGNEE="waqask";
GROUP BY changegroup.issueid
ORDER BY changegroup.CREATED DESC
) as b ON b.jid = jiraissue.id
WHERE jiraissue.ASSIGNEE="waqask";

The above query works if i use a specific jiraissue.id, but not if i search by jiraissue.ASSIGNEE (it carries on crunching).

Also I am interested in the latest resolved and assigned dates, there could be many, but i want the last one of each.

Any help is greatly appreciated.

Thank you.

Options: ReplyQuote


Subject
Written By
Posted
Joining different rows on same table with different critera
October 06, 2015 03:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.