Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple lookup AND vs OR conditions

Posted on 2011-09-27
4
Medium Priority
?
242 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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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 video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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