Joining fulltext search with another table

[stephan.gerlach]

Hi there,

I only just started playing around with fulltext search and I have the following scenario.

I got two tables. One called product and the other one price. Now this is a one to many as each product can have multiple prices (I am writing a script for a price comparison site).

So when someone performs a search I run the following query


$sql = "SELECT *, MATCH(name,description,isbn,author_names) AGAINST ('$pages[1]') AS score
FROM product
WHERE MATCH(name,description,isbn,author_names) AGAINST ('$pages[1]')
AND deleted=0
ORDER BY name ASC";


The problem is that I am getting also products where I have no prices available. The price table as a column called product which links to product.id

So I need to make a count on the prices and make sure its greater than 0.

Can anyone help? Hope i managed to describe my problem properly.

thanks in advance