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
