Solved

Multiple lookup AND vs OR conditions

Posted on 2011-09-27
4
234 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:dolythgoe
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
Comment Utility
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
 

Author Comment

by:dolythgoe
Comment Utility
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
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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
 

Author Closing Comment

by:dolythgoe
Comment Utility
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now