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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 a link table with source and destination

Author  Topic 

p-vd-berg
Starting Member

4 Posts

Posted - 2011-11-30 : 08:32:15
I have 3 tables

table: project
---------------
project_id
project_name

table: issue
--------------
issue_id
issue_name
issue_type
project_id

table: link
-------------
link_id
linktype
source
destination


you 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-2

But if I open issue_b and I create a link to issue_a the record will be:
2-10100-2-1

Depending 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'
union
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.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 - covered
10200 - Test Project - Requirement 2 sub 1 - NULL - Is not covered
10200 - Test Project - Requirement 2 sub 1 vtest template 2 - Is covered
10200 - Test Project - Requiremnet 1 sub 1 - NULL - Is not covered
10200 - Test Project - Requiremnet 1 sub 1 - Test template 1 - Is covered
10200 - Test Project - Requiremnet 1 sub 2 - NULL - Is not covered
10200 - Test Project - Software Requirement: No connections - NULL - Is not covered
10200 - Test Project - Software Requirement: No connections - test template 2 - Is covered

only 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
Go to Top of Page

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 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
left join JiraIssue DestIssue on DestIssue.ID = issuelink.DESTINATION
where JiraIssue.issuetype = '8'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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
left join JiraIssue DestIssue on DestIssue.ID = issuelink.DESTINATION
where JiraIssue.issuetype = '8'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Thanks visakh16!
This was exactly what i need
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 05:18:16
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -