Re: How to design
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.