Re: How to design
Posted by: Reto Baumann
Date: June 09, 2006 09:25AM

Let me put in an example (that's always good).

Let's create a test table:
CREATE TABLE `grouptest` (
`itemID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`groupID` INT UNSIGNED DEFAULT '0' NOT NULL ,
`keywords` VARCHAR( 250 ) NOT NULL ,
`score` SMALLINT UNSIGNED NOT NULL ,
`description` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `itemID` ) ,
INDEX ( `groupID` ) ,
FULLTEXT (
`keywords`
)
);

Insert some values:
insert into grouptest (groupID, keywords, score, description) values(1,'apple fruit tree',12,'A picture showing an apple tree');
insert into grouptest (groupID, keywords, score, description) values(1,'apple fruit',10,'Just an apple');
insert into grouptest (groupID, keywords, score, description) values(1,'apple rotten',9,'A rotten apple');
insert into grouptest (groupID, keywords, score, description) values(0,'apple wooden',22,'A wodden apple - unsorted');
insert into grouptest (groupID, keywords, score, description) values(0,'some strange apple',22,'An apple from outer space - unsorted');
insert into grouptest (groupID, keywords, score, description) values(2,'apple cooked',19,'A cooked apple');
insert into grouptest (groupID, keywords, score, description) values(3,'space shuttle',10,'A space shuttle');


If one would now search for 'apple' MySQL should return the following set:
itemID, groupID
1,1
4,0
5,0
6,2

(considering groupID 0 is the one returning all members, not just the 'best match').

How would an SQL for this look like?

thx
reto



Edited 1 time(s). Last edit at 06/09/2006 09:26AM by Reto Baumann.

Options: ReplyQuote


Subject
Written By
Posted
June 09, 2006 09:02AM
June 09, 2006 09:11AM
Re: How to design
June 09, 2006 09:25AM
June 09, 2006 12:54PM
June 11, 2006 12:54AM
June 11, 2006 10:02AM
June 11, 2006 11:31AM
June 11, 2006 12:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.