What's Wrong With This Query?

[hukadeeze]

I'm trying to run a full text search on two tables. One is a table populated with books, the other with cards. The term I'm using to search in the code below matches one record in the books table. When I run the first block of code, which queries just the books table, the correct record is returned. However, when I modify the query to include the cards table as well, mysql throws an error stating there are incorrent arguments for the MATCH clause (#1210 - Incorrect arguments to MATCH). Have I constructed this query incorrectly?

 

This query works:

SELECT Title, AuthorIllustrator, Form,
MATCH(Title, AuthorIllustrator, ISBN10, ISBN13)
AGAINST ('Amadito')
AS score
FROM books
WHERE MATCH(Title, AuthorIllustrator, ISBN10, ISBN13)
AGAINST ('Amadito')

 

This query does not work:

SELECT books.Title, books.AuthorIllustrator, books.Form, cards.Title, cards.Artist, cards.CardNumber, 
MATCH(books.Title, books.AuthorIllustrator, books.ISBN10, books.ISBN13, cards.Title, cards.Artist, cards.CardNumber) 
AGAINST ('Amadito') 
AS score 
FROM books,cards 
WHERE MATCH(books.Title, books.AuthorIllustrator, books.ISBN10, books.ISBN13, cards.Title, cards.Artist, cards.CardNumber)
AGAINST ('Amadito')

 

 

 

[DeadEvil]

try to use LEFT JOIN

 

SELECT books.Title, books.AuthorIllustrator, books.Form, cards.Title, cards.Artist, cards.CardNumber, 
MATCH(books.Title, books.AuthorIllustrator, books.ISBN10, books.ISBN13, cards.Title, cards.Artist, cards.CardNumber) 
AGAINST ('Amadito') 
AS score 
FROM books
LEFT JOIN cards ON book_id = cards_id 
WHERE MATCH(books.Title, books.AuthorIllustrator, books.ISBN10, books.ISBN13, cards.Title, cards.Artist, cards.CardNumber)
AGAINST ('Amadito')

[hukadeeze]

I tried that, and I still get the same error.

[hukadeeze]

Is there another way to full-text search the same term against two tables?

[Illusion]

what ever the columns u r passing for MATCH should be in SELECT statement too........(First u need to retrive the values then match them against the word u r searcing for as u r not retriving the all the columns so u r getting the error, I guess)

[hukadeeze]

sorry, that was an error in the posting. even when the select and match statements are equal mysql returns the same error. I did some research and found a post where someone said you can't full-text search more than one table.

[fenway]

sorry, that was an error in the posting. even when the select and match statements are equal mysql returns the same error. I did some research and found a post where someone said you can't full-text search more than one table.

 

That's true, an index can't span table.