JOIN 'n' MATCH query help

[essexboyracer]

Hi,

I want to do a MATCH across two tables and i think this means doing a JOIN, though i am a bit stumped as to how to achieve it.

Two tables:

CREATE TABLE recipe (
recipe_id int(11) NOT NULL auto_increment,
recipe_ingredientid int(11) NOT NULL default '0',
recipe_mealtypeid int(11) NOT NULL default '0',
recipe_name text NOT NULL,
recipe_fat int(11) NOT NULL default '0',
recipe_kcal int(11) NOT NULL default '0',
recipe_serves int(11) NOT NULL default '0',
PRIMARY KEY (recipe_id),
FULLTEXT KEY recipe_name (recipe_name)
) TYPE=MyISAM;

CREATE TABLE ingredients (
ingredient_id int(11) NOT NULL auto_increment,
ingredient_recipeid int(11) NOT NULL default '0',
ingredient_name text NOT NULL,
PRIMARY KEY (ingredient_id),
FULLTEXT KEY ingredient_name (ingredient_name)
) TYPE=MyISAM;

The Query:

SELECT recipe. ingredients. FROM recipe, ingredients LEFT JOIN ingredients.ingredient_recipeid ON recipe.recipe_ingredientid WHERE MATCH (ingredients.ingredient_name,recipe.recipe_name) AGAINST ('$keyword')

Thanks to anyone who can help me!

oli

[essexboyracer]

Is this where i am going wrong?

All parameters to the MATCH() function must be columns from the same table that is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN MODE.

http://www.mysql.com/doc/en/Fulltext_Restrictions.html

[TTT]

SELECT r. i.
FROM recipe r, LEFT JOIN ingredients i
ON i.ingredient_recipeid = r.recipe_ingredientid
WHERE "LRTRIM(i.ingredient_name)" = "LRTRIM(r.recipe_name)" AND "LRTRIM(r.recipient_name)" <> '$keyword'