Solved

Multiple lookup AND vs OR conditions

Posted on 2011-09-27
4
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
ID: 36713915
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
ID: 36713955
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
ID: 36713986
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
ID: 36900024
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

635 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