Multiple lookup AND vs OR conditions

Hi all,

I have this query:

SELECT  link_id FROM linkindex WHERE word_id IN (102,103,107,46,45,469,68,27,104,260)

Open in new window


This runs on a relationship table which has link_id and word_id columns. The PRIMARY is (word_id, link_id) so links can be repeated multiple times for different word ids and word_id repeated multiple for different links (I think this is called many-many!)

The query above is matching in an OR way - so gives me link_ids with 102 OR 103 OR 107 etc...

How would I accomplish this in the AND method so the link_id must have 102 AND 103 associated with it to be returned?

As always, big thanks!
dolythgoeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johanntagleConnect With a Mentor Commented:
select distinct a.link_id
from linkindex a join linkindex b on (a.link_id=b.link_id)
where a.word_id=102
and b.word_id=103

You need to know the exact number of words to match though that will translate to the number of joins.  If anybody knows how to do this without having to make N-1 joins I'd like to know too.
0
 
dolythgoeAuthor Commented:
Thanks for this - I will know the number of word_ids but they could get to as many as 10-15...

Is that 10-15 joins?
0
 
johanntagleCommented:
It's N-1 joins where N is your number of word_ids.  

Another way you can look at this is to store the word_ids in one column (not using standard many-many handling in RDBMS), like '102,103,107,46,45,469,68,27,104,260' then use full text index in boolean mode (http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html).  You have to make sure though that word_id has at least 3 digits else you will need ft_min_word_length (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_ft_min_word_len).  I personally don't like this method because I find MySQL full text search still very lacking in features and performance, but it's an option.
0
 
dolythgoeAuthor Commented:
Thanks - hopefully things like this will improve in mysql over time but the inner join method is fast although a bit clunky to write for lots of them!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.