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
Even on the small scale on which I'm operating, I'm aware that this
is horribly inefficient, but being the relative beginner I am I can't
see how to get the sectionid, caption and title from the first query
- though I daresay it'll be obvious to the experienced SQLers out
there!
I'm sure it's also a case of "If I were you, I wouldn't start from
here," but it's what I'm stuck with.
And - not wishing to push my luck, /but/ - if there's a nifty MySQL
5-type answer I'd be interested in that too.
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
The less a statesman amounts to, the more he loves the flag.
-- Kin Hubbard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
