A search using lots of keywords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

A search using lots of keywords

Author

Topic

krz
Starting Member

2 Posts

Posted - 2008-09-05 : 09:39:49

My task is to make a little searchengine for internal use at an architect office. They have about 1 000 DWG-details that they re-use every now and then. Unfortunately, finding the right detail is not very easy. Therefore they decided they wanted the possibility to do a quick search instead of going through 1 000 papersheets every time they needed a detail.

First of all, there is a predefined set of search keywords. Every detail will be given the appropriate keywords from this list. Due to this I made three tables:

tblDetails (DetailID is autonumber and primary key, Detail is text)


DetailID | Detail 
------------------
    1    | P00001
    2    | P00002
    3    | P00003
etc.

tblKeywords (KeywordID is autonumber and primary key, Keyword is text)


KeywordID | Keyword
--------------------
     1    | Vertical
     2    | Horisontal
     3    | Glass
     4    | Wall
etc. (at the moment there's about 34 keywords in this table)

tblConnected (DetailID and KeywordID are Long integers)


DetailID | KeywordID
--------------------
    1    |     1
    1    |     4
    2    |     1
    2    |     3
    2    |     4
    3    |     2
    3    |     3
etc.

I am not quite sure this is the best way to build the DB, but it was the best I could think of at the time (suggestions on how to rebuild the db would be much appreciated).

Anyway, the search will be done from an ASP based webpage. The interface will have one checkbox for every single keyword in the database. The user selects some of the keywords the detail needs to have and starts the search. Now, this is where the trouble starts.

First of all I need the query to return every detail that contains the keyword the user checked. Secondly I would also like it to return the number of keywords this detail actually is connected to in the DB.

So if a user checks Vertical and Wall, the query would return P00001 (2 (for two keywords in total)) and P00002 (3). If the user also checks glass it would return only P00002 (3).

The thing is, I can't get the query to work. Due to how tblConnected is built, I am having big problems getting it to return the correct result.

The query would have to accept variables from ASP, something like


WHERE keywordID = @keywordId1
AND keywordID = @keywordID2
AND keywordID = @keywordID3
AND keywordID = @keywordID4
AND keywordID = @keywordID5
etc.

Does anyone understand what I'm talking about? Can somebody please help me build the query (as an SQL query I can run directly in Access, converting it to ASP is no problem), because I am totally lost.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-05 : 10:01:24

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108740

E 12°55'05.63"
N 56°04'39.26"

krz
Starting Member

2 Posts

Posted - 2008-09-05 : 17:39:44

Thanks, Peso! Gonna try this out in the morning. I hope that link can bring an end to all my headscratching :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources