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.