Re: Complex search on a relationship
From: JohnFol (OutlookExpress_at_WibbleObbble.Com)
Date: 01/25/05
- Next message: Ricky Rock: "How do I use expressions (like "Or") in parameter prompts?"
- Previous message: saglamtimur: "Re: Select record 101 to 200 ???"
- In reply to: Gonzalo Moreno via AccessMonster.com: "Complex search on a relationship"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 Jan 2005 15:21:42 GMT
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?
"Gonzalo Moreno via AccessMonster.com" <forum@AccessMonster.com> wrote in
message news:25ab881074c340cc90e8bef52a98499b@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.
>
> --
> Message posted via http://www.accessmonster.com
- Next message: Ricky Rock: "How do I use expressions (like "Or") in parameter prompts?"
- Previous message: saglamtimur: "Re: Select record 101 to 200 ???"
- In reply to: Gonzalo Moreno via AccessMonster.com: "Complex search on a relationship"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
