April 19, 2008 at 8:42 am
Hello all,
I have to setup a search engine to look through a list of message. When users are sending their message they enter their title, their message and a list of keywords for their message.
This is perform from a form with the following fields
title, message, keywords
What's the best solution between below 2 options:
1- create one table that hold all datas as below:
table name: messages_table
id | title | message_body | keywords
18 | my title | my message1 | apple, tomato , cooking
and then if I want to look for messages that match with "tomato" and "cooking" i use the following request:
select id,title from messages_table where keywords like "cooking" keywords like "tomato"
2- Create 2 tables
one that hold information about message and one for keywords (one keywords per line)
table1 name: messages_table
id | title | message_body
18 | my title | my message
table2 name: keywords_table
id | id_message | keyword
1 | 18 | apple
2 | 18 | tomato
3 | 18 | cooking
then i have to do a self join if i want to look for "tomato" and "cooking" like this:
select messages_table.id,messages_table.title from messages_table, keywords_table as kwd0, keywords_table as kwd1 where messages_table.id=kwd0.id_message and kwd0.id_message=kwd1.id_message and kwd0.keyword="tomato" and kwd1.keyword="cooking"
Please note that I am using MS SQL server 7.0 , so I don't have the intersect operatot implemented
I hope I have been clear. I propose those 2 solutions but if someone as a third solution he is welcome.
thanks for your help
April 19, 2008 at 3:59 pm
SELECTid_message
FROMTable1
WHEREkeyword in ('tomato', 'cooking')
GROUP BYid_message
HAVINGCOUNT(DISTINCT keyword) = 2
SELECTid_message
FROMTable1
GROUP BYid_message
HAVINGMAX(CASE WHEN keyword = 'tomato' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN keyword = 'cooking' THEN 1 ELSE 0 END) = 1
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply