The following two sentences:
hello there
bye!
are represented in the table sentence_words by:
WORD_ID SENTENCE_ID WORD WORD_NUMBER
10 1 hello 1
11 1 there 2
12 2 bye! 1
I want to do an outer join query that gives me the results:
WORD1 WORD2
hello there
bye! NULL
Note that I may want to start in the middle of the sentence so I cannot assume that word2 has word_number = 2. If I choose my_start_number = 2 then the query should give me:
WORD1 WORD2
there NULL
I tried:
(my_start_number = 1)
select s1.word word1, s2.word word2
from sentence_words s1
left join sentence_words s2
on s1.sentence_id = s2.sentence_id
where s1.word_number = my_start_number
and (s2.word_number = s1.word_number +1 or s2.word_number is null);
That only gives me a result if there are two words in the sentence. I'm not sure what to do that isn't way complicated.
Dems answer is absolutely the correct one. I decided to write this answer to explain the reason that your original solution doesn't work. This is because you are trying to filter the following result set of the left outter join (showing all columns, with some names abbreviated to fit):
Now, take a look at your where clause:
... and it should be relatively easy to see why it doesn't work. For example,
s2.word_number
is neverNULL
.Move the
word_number + 1
requirement into theLEFT JOIN
.