1,105,633 Community Members

Something related to itself

Member Avatar
Dracata
Newbie Poster
2 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm making a fairly easy to edit and change public dictionary and am working on the viewer hooked to an SQL server. The problem I'm having is, isn't really a problem but I'm looking for a better solution, due to it possibly devolving into a huge mess of sorting a bunch of the same results.

Here is a basic version of my database with two of the tables:

TermDB

Table: Term
ID	Term
1	Alpha
2	Beta
3       Charlie
4	Gamma

Table: TermHMRelatedTerm
ID	TermID	RelatedTermID
1	1	2
2	1	3
3	2	1
4	2	3
5	2	4
6	3	2

So I've been using this query:

SELECT a.Term, b.Term FROM TermDB.dbo.Term a JOIN TermDB.dbo.TermHMRelatedTerm c ON a.ID=c.TermID, TermDB.dbo.Term b JOIN TermDB.dbo.TermHMRelatedTerm d ON b.ID=d.RelatedTermID WHERE a.ID=d.TermID

Current Output:

Alpha	Beta
Alpha	Charlie
Alpha	Beta
Alpha	Charlie
Beta	Alpha
Beta	Charlie
Beta	Gamma
Beta	Alpha
Beta	Charlie
Beta	Gamma
Beta	Alpha
Beta	Charlie
Beta	Gamma
Gamma	Beta

As you can see I have duplicates equal to the number of related terms. Output I'd like to have:

Alpha	Beta
Alpha	Charlie
Beta	Alpha
Beta	Charlie
Beta	Gamma
Gamma	Beta

Does anyone know a way to do a better query?

Member Avatar
Momerath
Senior Poster
3,832 posts since Aug 2010
Reputation Points: 1,327 [?]
Q&As Helped to Solve: 664 [?]
Skill Endorsements: 19 [?]
Featured
 
1
 

SELECT a.Term, b.Term From Term a, Term b, TermHMRelatedTerm c
WHERE a.ID = c.TermID and b.ID = c.RelatedTermID

Just FYI, there is no Gamma->Beta (there is Beta->Gamma) and your output is missing Charlie->Beta (in your 'what I want' data).

Member Avatar
Dracata
Newbie Poster
2 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks that worked perfectly. Heh, yea that is what I get for not double checking the results when I added in Charlie to show more behavior with Beta.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: