SQL left self-join with WHERE clause dependencies between two copies of a table

Question

The following two sentences:

hello there bye! 

presented in the words_words table:

 WORD_ID SENTENCE_ID WORD WORD_NUMBER 10 1 hello 1 11 1 there 2 12 2 bye! 1 

I want to make an external connection request that gives me the results:

 WORD1 WORD2 hello there bye! NULL 

Please note that I can start in the middle of the sentence, so I cannot assume that word2 has word_number = 2. If I select my_start_number = 2, then the request 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); 

It only gives me the result if there are two words in the sentence. I’m not sure that this is not difficult.

Answer 1

Move the requirement word_number + 1 to the LEFT JOIN .

 SELECT s1.word word1, s2.word word2 FROM sentence_words s1 LEFT JOIN sentence_words s2 ON s2.sentence_id = s1.sentence_id AND s2.word_number = s1.word_number + 1 WHERE s1.word_number = my_start_number 

Answer 2

Dems answer is absolutely correct. I decided to write this answer to explain why your original solution does not work. This is because you are trying to filter out the following result set of a join with a left outer join (showing all columns with some abbreviated names):

 s1.WORD_ID s1.SENT_ID s1.WORD s1.WORD_NUM s2.WORD_ID s2.SENT_ID s2.WORD s2.WORD_NUM 10 1 hello 1 10 1 hello 1 10 1 hello 1 11 1 there 2 11 1 there 2 10 1 hello 1 11 1 there 2 11 1 there 2 12 2 bye! 1 12 2 bye! 1 

Now take a look at your where clause:

 where s1.word_number = my_start_number and (s2.word_number = s1.word_number +1 or s2.word_number is null); 

... and it should be relatively easy to understand why this is not working. For example, s2.word_number never NULL .