I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words.
Here is an example table.
Ok, now say that someone queried the database for the words that had WORDIDs 4, 2, and 5.
My erroneous SQL SELECT statement would be something like:
Select docid from table where wordid = 4 and wordid = 2 and wordid = 5
This is giving me 0 results.
I have seen in other places where the
where in clause has been suggested:
If I understand correctly, this is another way to write an OR clause. I have tried this:
select docid from table where wordid in (4,2,5)
But, this is giving me all the results. It should exclude docid 2 as that does not contain the other words. I'm expecting to just get docid 1.
However, I could be using the
where in clause incorrectly as I have very little db experience.
How can I return docids that contain all of the words?
Please note as well, my where clause will be dynamically generated in a FOR loop. The query could be as simple as one or two words, or it could be 10 or 12 words. I'm looking for a query structure that takes speed into consideration. Please let me know if you need anymore information.
For reference, I am trying to convert this code into PHP / MYSQL, but I don't understand the sql statement here or its equivalent in MYSQL: