SQL Server Forums
Profile | Register | 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		  New Topic  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
1435 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
48041 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
48041 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000