Hi Eric,

You're looking at a self-join, and I think an inner join. I assume that position is unique within the table. You might try something like so:

select x.key, x.word
from word_table w
inner join word_table x 
  on (x.position = w.position - 1
        and x.part_of_speech = 'adjective')
where w.word = 'king'

Depending on the size of the table, the database implementation, and the exact structure of the query, this might get real slow real fast.

Since you know that full text indexers are much better at this, I assume that you have some compelling reason for wanting to do this in SQL. Just be aware that you might have some performance issues. Put an index on the position column, or change your keys into something you can do arithmetic on, that might buy you something.

-Tod


> On Jul 22, 2017, at 7:18 AM, Eric Lease Morgan <[log in to unmask]> wrote:
> 
> How might I write an SQL query to find all the descriptions of good kings and bad kings in my database?
> 
> I have a table that looks something like this:
> 
>  key  word       part_of_speech  position
>  ---  ---------  --------------  --------
>  w1   The        article         1
>  w2   righteous  adjective       2
>  w3   king       noun            3
>  w4   solved     verb            4
>  w5   all        adjective       5
>  w6   the        article         6
>  w7   problems   noun            7
>  w8   Your       noun            8
>  w9   evil       adjective       9
>  w10  king       noun            10
>  w11  over       adverb          11
>  w12  taxes      verb            12
>  w13  us         pronoun         13
> 
> And I want to list how kings are described. In this example, kings are both righteous as well as evil. How can I implement this in pure SQL? How do I find all words where the given word is "king" and the previous word is an adjective?
> 
> —
> Eric Morgan