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.
docid wordid 1 4 2 4 1 2 1 5
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: