Complex search on a relationship

  • Thread starter Thread starter Gonzalo Moreno via AccessMonster.com
  • Start date Start date
G

Gonzalo Moreno via AccessMonster.com

Hi,

I'm having a lot of trouble trying to figure out how to let the user building complex search conditions on a relationship between articles and keywords belonging to those articles.

It's supossed to be a very simple thing but I can't work it out, let me explain you:

For this matter there are only three tables:
Articles (id_article, title)
Keywords (id_keyword, keyword)
Keywords_Articles (id_keywords_article, id_keyword, id_article).

Well, a simple N-N relationship.

Now I want to search something like this:

articles that contains keywords ("ASPIRIN" OR "AAS") AND that contains ("TTO").

I think this would be done with an UNION

SELECT title
FROM Article
WHERE id_article IN
(SELECT id_article
FROM Keywords_Articles
WHERE keyword = 'ASPIRIN' OR keyword = 'AAS')
UNION
SELCT title
FROM Article
WHERE id_article IN
(SELECT Keywords_Articles
WHERE keyword = 'TTO')

Am I right? Is there a better aproach?

Well, that's not all, what if the user want's something like:

Articles that contains (("AAS" AND "TTO") OR ("HUMAN")) AND ("SID")
I don't know if I should limit the search capabilities to just
("word" OR "word" OR "word") AND ("word" OR "word" OR ...) AND ...
("ANDs" of "ORs")

or maybe ("ORs" of "ANDs"):
SELECT ..
FROM ..
WHERE .. IN (SELECT .. FROM .. WHERE .. = .. OR .. = ..)
AND .. IN (SELECT .. FORM .. WHERE .. = .. OR .. = .. OR...)


I thought of letting a not normalized DB having all keywords in one field (that, apart of beeing a waste of space could lead to input errors, etc., but would the SELECT be far easier...)

Please, bring some light into this matter, i'm going nuts! :-)

Thanks!
Gonzalo.
 
I broke this down into a pair of queries. The first links the keywords to
the keyword article lnking table

SELECT Keywords_Articles.id_article, KeyWords.keyword
FROM KeyWords INNER JOIN Keywords_Articles ON KeyWords.id_keyword =
Keywords_Articles.id_keyword;

I called this KeysAndArticles
I then used this with the Articles table in another query

SELECT Articles.title, KeysAndArticles_1.keyword, Articles.id_article
FROM KeysAndArticles AS KeysAndArticles_1 INNER JOIN Articles ON
KeysAndArticles_1.id_article = Articles.id_article
WHERE (((KeysAndArticles_1.keyword)="asprin" Or
(KeysAndArticles_1.keyword)="AAS") AND ((Articles.id_article) In (select
id_article from keysandarticles where keyword = "TTO")));


worth a try?
 
Back
Top