Solved

Multiple lookup AND vs OR conditions

Posted on 2011-09-27
4
236 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
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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