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
