SQL query: multiple combinations
 

Hi.

Need help with a SQL query.

I need to be able to search after multiple words and get the unique text that the words belong to.

To simplify it for you, I have two tables:

text
--------------
text.id
text.word_id

word
--------------
word.id
word.word (dog, bird, frog....)

Now I have a couple of texts:
-----------------------------------------------
Text 1: "dog frog bird" (gets text.id = 1)
Text 2: "dog cat mouse" (gets text.id = 2)
Text 3: "cow dog bird" (gets text.id = 3)
-----------------------------------------------

I now split up the text into the different words:
-----------------------------------------------
dog gets (word.id = 1, text.id = 1)
frog gets (word.id = 2, text.id = 1)
bird gets (word.id = 3, text.id = 1)

dog gets (word.id = 1 [since dog already exits], text.id = 2)
cat gets (word.id = 4, text.id = 2)
mouse gets (word.id = 5, text.id = 2)

cow gets (word.id = 6, text.id = 3)
dog gets (word.id = 1 [since dog already exits], text.id = 3)
bird gets (word.id = 3 [since bird already exists], text.id = 3)
-----------------------------------------------

Now I want to make a search

If I search for 'cat' I get text.id = 2
If I search for 'dog' I get text.id = 1,2,3
If I search for 'bird' I get text.id = 1,3

and so on.

But I want a SQL query that lets me search for multiple animal at one go

If I search for 'cat' AND 'dog' I get text.id = 2
If I search for 'dog' AND 'bird' I get text.id = 1,3

and so on.

Also you must be able to search after multiple word, dog, cat, wolf, pig and so on at one go.

The query that I used that failed was:
-----------------------------------------------
SELECT DISTINCT text.id FROM text
LEFT JOIN word ON (word.id = text.word_id)
WHERE word.word LIKE '%dog%' AND word.word LIKE '%bird%'
-----------------------------------------------

I have tried diffrent JOINS but no luck, what must I do, a select in a select, a new table... I'am running out of ideas.

I use LIKE because you may want to search after 'hippo' and stil get 'hippopotamus'.

Thanks in advanced

/krister

try an inner join rather then a left join.

Sorry don't work.

When I run this SQL query:
-------------------------------------------------------
SELECT DISTINCT a_text.id FROM a_text
INNER JOIN a_word ON (a_word.id = a_text.word_id)
WHERE a_word.word LIKE '%dog%' AND a_word.word LIKE '%bird%'
-------------------------------------------------------

(I had to rename my tables to a_**** so they don't effect anything else )

I have these values in my tables

a_text: (id, word_id)
-----------------------
1, 1
1, 2
1, 3
2, 1
2, 4
2, 5
3, 1
3, 3
3, 6

a_word: (id, word)
-----------------------
1, dog
2, frog
3, bird
4, cat
5, mouse
6, cow

So if I search for 'frog' I get -> 1 (ok), 'dog' - > 1,2,3 (ok)
But that is just with one 'LIKE' (WHERE a_word.word LIKE '%dog%')

I want to be able to search with multiple 'LIKE', 'dog' and 'bird' should get me -> 1,3, now it gets me nothing

Anyone got any ideas?

/Krister

For multiple searches you will be looking for dog OR bird. Not dog AND bird

If by this you mean to return the a_text.id which has all the searched members in it's records, e.g. given your example searching for "dog","cat","mouse" would return a_text.id=2 then this should do it - you'll need to adjust the having term for the number of members you are searching for when it's not the exampled three:
You could wrap that up to count the number of members you are searching for and drop the result in your query to save tailoring it each time. So as it stands a search for "dog" and "bird" would be done like this - note the adjusted "having count(distinct word_id)=2" as you are now searching for two terms not three:returning id's of 1 and 3 as these text entries exist with both "dog" and "bird".

Thanks bgeddy.

That works better then my SQL query and it moves me forward towards the end.

Now I need to be able to use LIKE in the query since you should be able to search for "hippo" and still get "hippopotamus".

I think I only need to add my 'LIKE' query where your select word query is added.

/Krister