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 <[email protected]> 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
