Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
p-vd-berg
Starting Member
4 Posts |
Posted - 2011-11-30 : 08:32:15
|
I have 3 tablestable: project---------------project_idproject_nametable: issue--------------issue_idissue_nameissue_typeproject_idtable: link-------------link_idlinktypesourcedestinationyou can link issues together with the program. If I open issue_a (issue_id=1) and I create a link with issue_b (issue_id=2)the record in table: link is as follow (10100 is the linktype)1-10100-1-2But if I open issue_b and I create a link to issue_a the record will be:2-10100-2-1Depending on where you create the link, the issue can be the source or the destination I have this query: select Project.id, Project.pname, JiraIssue.SUMMARY, SourceIssue.Summary as [Gelinkt aan], case when IssueLink.Source is null then 'Is not covered' else 'Is covered' end as [Covered] from JiraIssue inner join Project on JiraIssue.PROJECT = project.ID left join IssueLink on JiraIssue.ID = issuelink.DESTINATION and issuelink.linkType = '10100' left join JiraIssue SourceIssue on SourceIssue.ID = issuelink.Source where JiraIssue.issuetype = '8'unionselect Project.id, Project.pname, JiraIssue.SUMMARY, SourceIssue.Summary as [Gelinkt aan], case when IssueLink.Source is null then 'Is not covered' else 'Is covered' end as [Covered] from JiraIssue inner join Project on JiraIssue.PROJECT = project.ID left join IssueLink on JiraIssue.ID = issuelink.SOURCE and issuelink.linkType = '10100' left join JiraIssue SourceIssue on SourceIssue.ID = issuelink.DESTINATION where JiraIssue.issuetype = '8'The result of the query will be: projid - project_name - summary - linkedto - covered10200 - Test Project - Requirement 2 sub 1 - NULL - Is not covered10200 - Test Project - Requirement 2 sub 1 vtest template 2 - Is covered10200 - Test Project - Requiremnet 1 sub 1 - NULL - Is not covered10200 - Test Project - Requiremnet 1 sub 1 - Test template 1 - Is covered10200 - Test Project - Requiremnet 1 sub 2 - NULL - Is not covered10200 - Test Project - Software Requirement: No connections - NULL - Is not covered10200 - Test Project - Software Requirement: No connections - test template 2 - Is coveredonly the records that are linked are shown two times. Which makes sense...But how can I fix this (with a small example)? |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-11-30 : 10:57:51
|
What would you like the output to look like?=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-30 : 10:58:26
|
[code]select Project.id, Project.pname, JiraIssue.SUMMARY, COALESCE(SourceIssue.Summary,DestIssue.Summary) as [Gelinkt aan],case when IssueLink.Source is null and IssueLink.DESTINATION is null then 'Is not covered' else 'Is covered' end as [Covered]from JiraIssueinner join Project on JiraIssue.PROJECT = project.IDleft join IssueLink on JiraIssue.ID = issuelink.DESTINATION and issuelink.linkType = '10100'left join JiraIssue SourceIssue on SourceIssue.ID = issuelink.Sourceleft join JiraIssue DestIssue on DestIssue.ID = issuelink.DESTINATIONwhere JiraIssue.issuetype = '8'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
p-vd-berg
Starting Member
4 Posts |
Posted - 2011-12-01 : 05:12:27
|
quote: Originally posted by visakh16
select Project.id, Project.pname, JiraIssue.SUMMARY, COALESCE(SourceIssue.Summary,DestIssue.Summary) as [Gelinkt aan],case when IssueLink.Source is null and IssueLink.DESTINATION is null then 'Is not covered' else 'Is covered' end as [Covered]from JiraIssueinner join Project on JiraIssue.PROJECT = project.IDleft join IssueLink on JiraIssue.ID = issuelink.DESTINATION and issuelink.linkType = '10100'left join JiraIssue SourceIssue on SourceIssue.ID = issuelink.Sourceleft join JiraIssue DestIssue on DestIssue.ID = issuelink.DESTINATIONwhere JiraIssue.issuetype = '8' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thanks visakh16!This was exactly what i need |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-01 : 05:18:16
|
wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|