Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 a link table with source and destination
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

p-vd-berg
Starting Member

4 Posts

Posted - 11/30/2011 :  08:32:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 11/30/2011 :  10:57:51  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/30/2011 :  10:58:26  Show Profile  Reply with Quote

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/

Go to Top of Page

p-vd-berg
Starting Member

4 Posts

Posted - 12/01/2011 :  05:12:27  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 12/01/2011 :  05:18:16  Show Profile  Reply with Quote
wc

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000