a simple sql question ( i hope)

zak

I have table of the following:

word_id | word_position | page_reference

I want to be able to search it for two word_id's, say 17 and 20 that are 1 number apart from each other in the word_position column and for it to return the page reference.

Can you help me with this please?

realtek

Hi zak,

I'm not sure if I understand your question correctly.

if you wanted to search for two numbers in your word_id column by a range try the following:

Select page_reference from word_id where word_id between '17' and '20';

This would return only the page_reference

Hope this helps

zak

Thanks for replying so quickly!

What i want to do is:

I have already broken down a user's search term and got the various word_id's for each word from another table. If the user has searched for 2 words which just happen to have the id's of 17 and 20, i want to do the following:

here is the table again:

word_id | word_position | page_reference

I want to select the value of page_ref when the word_position value of word_id 17 is 1 value higher than the word_position of word_id 20. So for example, i want a search of the following table for word_id 17 and word_id 20:

word_id | word_position | page_reference
========================================
17      |       94      |       5
----------------------------------------
20      |       35      |       7           

----------------------------------------
24      |       67      |       11
----------------------------------------
17      |       53      |       18
----------------------------------------
20      |       95      |       5
----------------------------------------
20      |       54      |       18

(i had to use code to get the layout right)

to produce the following output:

page_ref

5
18

since in this random example, 5 and 18 are the only two which satisfy the query. I hope this clears it up a bit. Thanks.

ahundiak

I don't think it's going to be simple.

You would be asking sql to find two records, subtract the word_position values then return the record pairs where the delta is one.

You could do this in php. One query for word_id = 17 and one for word_id = 20. Then bounce each result against each other looking for the desired delta. If there are hundreds of records for each word_id then this would time consuming.

A second approach would be to make a delta table.
word_id1
word_id2
word_delta
page_reference

It would be time consuming to generate such a table but the query would be quick and easy.

One thing you could do is to use the php approach the first time a user queried a pair of words and then store the results in the delta table for subsequent use. A cache.

nnichols

I don't think it can be done with a single query. You will have to use something like this -

SELECT word_position, page_reference FROM table WHERE word_id=17

and then use the result of the query to build a second query -

SELECT page_reference FROM table WHERE word_id=20
AND (
word_position=(word_pos1 from prev query+1)
OR word_position=(word_pos2 from prev query+1)
OR
and so on...
)

Hope this helps 😉

nnichols

I was having a thick moment. Try -

SELECT *
FROM word a, word b
WHERE b.word_position=(a.word_position+1)
AND a.word_id=17
AND b.word_id=20

Hope this helps 😉

zak

Thanks for the help, guys. 😃

A cache sounds like an excellent idea!

One question:

when you say:

FROM word a, word b

how should that be constrcuted? ie. Are they 2 tables?

Thanks for taking the time to reply.

nnichols

"word" should be changed to the name of your table, and it is aliased twice with a, b.

zak

You. my friend, are a genius! I've been trying to work this out for weeks. Thanks a lot.