Re: Newbie: Help with Match without using a subQuery

[Graham Anderson]

many thanks
that was exactly what I needed.....
Building upon the initial question:
Is there a way to get Match statements to do partial word 
searches...like %LIKE% ?

For example, if the below Match Against query  with 'chris' does not 
bring up anything...
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)

Query the database again with 'chris*' ?  Is querying the database(if 
the first query fails)  twice the best way to do this?
AND MATCH (
media.name, media.product
)
AGAINST (
'chris*'
)

Is there a standard way of doing this ?
thanks Shawn :)
g
On Apr 13, 2005, at 11:00 AM, [EMAIL PROTECTED] wrote:
Graham Anderson <[EMAIL PROTECTED]> wrote on 04/13/2005 01:46:35 PM:
Hi
I am trying to set up my Match statements to filter the result of the
main query [which works]
If 'chris' does not exist in the first MATCH statement [AND MATCH
(media.name, media.product)], then the results get a bit screwed up :(
If the first match statement finds something, then the query works
fine....
What would be a better way to structure this...without using a 
subquery
as I am on MYSQL 3.23

SELECT media.id, media.product AS product, media.name AS name,
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id 
AS
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id

AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30

it's a parentheses issue. Your query looks like this in the where 
clause

WHERE artist_ID AND mediaType_ID AND first match OR second match
Which gets evaluated like
WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
Any record that matched your second match condition also satisfied your
WHERE clause. Because you wanted to match on artist_Id and mediaType_ID
plus one of the match conditions, you needed to put a set of 
parentheses
around BOTH of your match conditions so that your WHERE clause looked
like:

WHERE artist_ID AND mediaType_ID AND (first match OR second match)
Here is an updated version of your original query (I also changed your
implicit inner joins to explicit ones (it's a pet peeve)):
SELECT media.id, media.product AS product
        , media.name AS name
        , Concat_WS( ' ', artist.fname, artist.lname ) AS artist
        , mediaType.id AS mediatype
        , mediaType.name
        , media.product
        , media.path
FROM media
INNER JOIN mediaType
        ON media.mediaType_id = mediaType.id
INNER JOIN artist
        ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
        AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
        AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]