SQLTeam.com | Weblogs | Forums

Select question

I have a table with 3 cols
the pluralId points to the id of another word in the same table.
can I select in a single query the word (singular) and its plural version?

this works, but it seems very complecated, there must be a better way.
or maybe I have to put the plural ver of the word in the 3rd col instead of the pluralId?

select a.word,(select b.word from words as b where b.id=a.pluralId) as plural from words as a where a.id=64
select a.word
      ,b.word as plural
  from words as a
       left outer join words as b
                    on b.id=a.pluralid
 where a.id=64

But in your solution the table words is refered twice.
maybe if there is no simple query to do it the best way would be to save the plural word itself in the third col and not the pluralId?

Same as in your solution.

I am looking for a better solution