Hi, 2006/6/28, Chris Sansom <[EMAIL PROTECTED]>:
I'm sure this is an elementary problem, but I can't get my head round it.I have two tables: pix and sections, the relevant bits of which are: pix (2,421 rows): picid varchar(7) not null sectionid smallint(5) unsigned not null caption text null picid and sectionid are a joint primary key caption is full text indexed sections (a mere 152 rows): sectionid smallint(5) unsigned not null title varchar(63) not null blurb text null sectionid is primary key (auto increment) title and blurb are full text indexed In pix, there may well be several instances of the same picid, but always with a different sectionid (obviously). The trouble is, this was originally set up with no intention of actually searching the tables, but now I want to. And I want to find the first instance of each picid that matches the text anywhere in caption, title or blurb, and get some other info at the same time. Oh, and for the time being it needs to be possible in MySQL 3.23.x. So far I'm doing a very simple: SELECT DISTINCT picid FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%' ORDER BY picid then as I loop through the results I'm more or less repeating the process to get the other information: SELECT p.sectionid, caption, title FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') AND p.picid = 'picid' LIMIT 1
If you know the picid previously retrieved, then the clause (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') is redundant, isn't it ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
