Something related to itself

[Dracata]

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?

[Momerath]

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).

[Dracata]

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.