MySQL help

[Ngreth Thergn]

ok, I have the following.

SELECT items.item_name, items.item_id, recipes.recipe_id
FROM `recipes` 
WHERE recipes.item_id = items.item_id
AND recipes.recipe_id
IN (
  SELECT recipe_components_map.recipe_id
  FROM recipe_components_map, items
  WHERE items.item_id = recipe_components_map.item_id
  AND items.item_name LIKE "%Book of Wood Elven Culture%"
)

it gives the following error:

MySQL said:  

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT recipe_components_map.recipe_id
FROM recipe_components_

I have checked a rechecked the table and row names and I think they are all correct.
Can anyone see what I am doing wrong? I am trying to find all recipies that use an item with a partial name of Book of Wood Elven Culture.
this takes a spread across 3 tables.

• items and recipies are linked by item_id. (in the recipes table) This item_id is teh result of the recipe.
• the name of an item is only in the items table.
• recipe_components_map links recipies and items. I.E. is is the "relation" table between the two. it consists of a recipe_id and an item_id (and an ammount, but that is not important to this). it is a many to many relationship.

simplfied table structure: (simplified to only what is important for this query)

-- 
-- Table structure for table `items`
-- 

CREATE TABLE `items` (
  `item_id` int(11) unsigned NOT NULL auto_increment,
  `item_name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`item_id`),

) TYPE=MyISAM COMMENT='Everquest Items Table.  Tradeskill related items only.' AUTO_INCREMENT=20312 ;

-- 
-- Table structure for table `recipes`
-- 

CREATE TABLE `recipes` (
  `recipe_id` int(11) unsigned NOT NULL auto_increment,
  `item_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`recipe_id`),
  KEY `item_id_IDX` (`item_id`)
) TYPE=MyISAM AUTO_INCREMENT=20472 ;

-- 
-- Table structure for table `recipe_components_map`
-- 

CREATE TABLE `recipe_components_map` (
  `recipe_id` int(11) unsigned NOT NULL default '0',
  `item_id` int(11) unsigned NOT NULL default '0',
  `amount` tinyint(2) unsigned NOT NULL default '1',
  PRIMARY KEY  (`recipe_id`,`item_id`)
) TYPE=MyISAM;

I ran

SELECT recipe_components_map.recipe_id
  FROM recipe_components_map, items
  WHERE items.item_id = recipe_components_map.item_id
  AND items.item_name LIKE "%Book of Wood Elven Culture%"

by itself and got a good query. Maybe I am using the nested selects incorectly?

Basically I am going to want to update a column in all recipes rows that match the inner query, I am doing the big select to confirm that I have all the correct stuff before I make the update.

I.E. eventually I want

UPDATE recipes 
SET race = ELF 
WHERE recipe_id 
IN (
SELECT recipe_components_map.recipe_id
  FROM recipe_components_map, items
  WHERE items.item_id = recipe_components_map.item_id
  AND items.item_name LIKE "%Book of Wood Elven Culture%"
)

but I want to confirm BEFORE I actually take that step.

[KyrosKrane]

First thing that jumped out at me:

SELECT items.item_name, items.item_id, recipes.recipe_id
   FROM `recipes`

You're selecting from the items table, but it's not in your FROM clause. That's usually a no-no.

Try changing the FROM clause to this:

FROM `recipes`, `items`

If it still fails, try removing the second condition from your WHERE clause (the part with the inner query) and see if the outer query will run.

I'm trying to see if I can rewrite this into a join so you don't have to use an IN clause at all.

[Ngreth Thergn]

that was a "DUH" error sorry

so new code is:

SELECT items.item_name, items.item_id, recipes.recipe_id
FROM `recipes` , `items` 
WHERE recipes.item_id = items.item_id
AND recipes.recipe_id
IN (
  SELECT recipe_components_map.recipe_id
  FROM recipe_components_map, items
  WHERE items.item_id = recipe_components_map.item_id
  AND items.item_name LIKE "%Book of Wood Elven Culture%"
)

it gets the same error :/

[KyrosKrane]

I'm not sure if I got this right ... it took me a while to figure this out. Tell me if this runs at all:

SELECT 
 	Result.item_name, 
 	Result.item_id, 
 	Result.recipe_id
 FROM  
 	`recipes`
 	LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
 	LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.item_id
 	LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
 WHERE 
 	Component.item_name LIKE "%Book of Wood Elven Culture%"

[KyrosKrane]

Oh, BTW, from the PHP documentation:

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With MySQL versions prior to 4.1, it was necessary to work around or avoid the use of subqueries. In many cases, subqueries can successfully be rewritten using joins and other methods. See Section 13.1.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

What version of MySQL are you running?

[Ngreth Thergn]

[QUOTE=KyrosKrane]I'm not sure if I got this right ... it took me a while to figure this out. Tell me if this runs at all:

[code]SELECT
Result.item_name,
Result.item_id,
Result.recipe_id
FROM
`recipes`
LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.item_id
LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
WHERE

this gets:

#1054 - Unknown column 'Result.recipe_id' in 'field list'

and it is MySQL 4.0.22-standard

so that probably means my Update will not work.

[KyrosKrane]

My turn to make a DUH error. =) The Recipe_ID has to be in the recipes table. =)

Here's the correction.

SELECT 
  	Result.item_name, 
  	Result.item_id, 
  	recipes.recipe_id
  FROM  
  	`recipes`
  	LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
  	LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.item_id
  	LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
  WHERE 
  	Component.item_name LIKE "%Book of Wood Elven Culture%"

[Ngreth Thergn]

My turn to make a DUH error. =) The Recipe_ID has to be in the recipes table. =)

Here's the correction.

SELECT 
  	Result.item_name, 
  	Result.item_id, 
  	recipes.recipe_id
  FROM  
  	`recipes`
  	LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
  	LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.item_id
  	LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
  WHERE 
  	Component.item_name LIKE "%Book of Wood Elven Culture%"

Something seems to be off in this becuase the server is timing out on it
Do you think I should just give up on an elegant MySQL solution and do it as multiple queries and use PHP to dig out what I need?

[Turlo Lomon]

Bare with me, as I am a bit rusty, but I think I found your error. It looks like you are causing a full database search by cross referencing what is most likely your largest database with itself. This is where your timeout is most likely occurring.

Also, you stated above that your recipes are linked to the recipe_components_map by recipe_id, but you have it referencing an items_id, which could cause a problem as well.

SELECT
     items.item_name, 
     items.item_id, 
     recipes.recipe_id
FROM  
     recipes
     LEFT JOIN recipe_components_map ON recipes.recipe_id=recipe_components_map.recipe_id
     LEFT JOIN items ON items.item_id = recipe_components_map.item_id
WHERE 
     items.item_name LIKE "%Book of Wood Elven Culture%"

I cleaned up the FROM section. I think that is where a majority of your problem is.

Edit: Updating this a bit. Found additional way to optimize it and found something that was needed from your very first post.

[KyrosKrane]

Turlo, the problem is you need to access the Items table twice. Once for the final result, and once for the component. As I understand it, Ngreth essentially wants a list of the following type:

Final_Item_01, Final_Item_ID_01, Recipe_01_to_make_Final_Item_01
Final_Item_01, Final_Item_ID_01, Recipe_02_to_make_Final_Item_01
Final_Item_02, Final_Item_ID_02, Recipe_01_to_make_Final_Item_02
etc.

The catch is, he wants a list of items whose recipe contains another item (specifically, one whose name is like "%Book of Wood Elven Culture%"). Your code instead searches for final items with that name.

What I was trying to do was reference the Items table twice, once for the final results and once for components for each recipe. Yes, that join is a major mess, as it links the three biggest tables (twice on the items table, no less) and results in one line for every single component for every single recipe -- and then it has to be filtered for components with a matching name. I'm not surprised it's timing out.

On the other hand, I'm not entirely sure if the search can be done any other way without using a sub-query ... I'll mull this over some more.

[KyrosKrane]

OK, I found another bug in my code that not only would have resulted in nonsensical results, but was probably why the code was timing out. Once more unto the breach ...

SELECT DISTINCT
   	Result.item_name, 
   	Result.item_id, 
   	recipes.recipe_id
 FROM  
   	`recipes`
   	LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
   	LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.recipe_id
   	LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
 WHERE 
   	Component.item_name LIKE "%Book of Wood Elven Culture%"

If this doesn't work, the query can be simplified a bunch if you can use the item ID of the Book of Wood Elven Culture, rather than the name.

[Turlo Lomon]

Sorry to poke my nose in this again, but I find it fascinating, and I used to work on exact problems like this full time... um... many years ago. Honestly, I miss working with this type of code, but if you would rather I hush and step back into the shadows, no problem. I understand.

However, I think I found the problem this time after you explained exactly what was being looked for. LEFT JOIN will bring up records that may not apply to the rest of the query. INNER JOIN seems to be more appropriate.

SELECT DISTINCT

Result.item_name,
Result.item_id,
recipes.recipe_id

FROM 

'items' Component
INNER JOIN 'recipe_components_map' ON recipe_components_map.item_id=Component.item_id
INNER JOIN 'recipes' ON recipes.recipe_id=recipe_components_map.recipe_id
INNER JOIN 'items' Result ON recipes.item_id = Result.item_id

WHERE

Component.item_name LIKE "%Book of Wood Elven Culture%"

First, the order of the database initializations appeared backwards. This order should provide the results you want.

1) Finds entries for the book in items. This is the base to drive the rest of the query.
2) Cross references the books with recipe maps that have the books as part of them.
3) Cross references the recipe map with the recipe.
4) Cross references the recipe back to the item to find out the name.

The first query will take a bit, but the rest should run fairly fast. Alternatively, I think that not using JOIN commands may actually work better due to the potentional for entries that simply do not exist.

SELECT DISTINCT

Result.item_name,
Result.item_id,
recipes.recipe_id

FROM 

'items' Component, 'recipe_components_map', 'recipes', 'items' Result

WHERE

Component.item_name LIKE "%Book of Wood Elven Culture%"
AND Component.item_id=recipe_components_map.item_id
AND recipe_components_map.recipe_id=recipes.recipe_id
AND recipes.recipe_id=Result.item_id

Oh well. I think I am out of ideas. I will most likely monitor this thread to see how it turns out. I am now curious what the resolution will be.

[Ngreth Thergn]

OK, I found another bug in my code that not only would have resulted in nonsensical results, but was probably why the code was timing out. Once more unto the breach ...

SELECT DISTINCT
   	Result.item_name, 
   	Result.item_id, 
   	recipes.recipe_id
 FROM  
   	`recipes`
   	LEFT JOIN `items` Result ON recipes.item_id = Result.item_id
   	LEFT JOIN `recipe_components_map` ON recipes.recipe_id=recipe_components_map.recipe_id
   	LEFT JOIN `items` Component ON Component.item_id = recipe_components_map.item_id
 WHERE 
   	Component.item_name LIKE "%Book of Wood Elven Culture%"

If this doesn't work, the query can be simplified a bunch if you can use the item ID of the Book of Wood Elven Culture, rather than the name.

This one seemed to work I am not using a specific item ID because there are 4 item ID's that match that query, and I want to try and do all 4 in one sweep, WITHOUT having a seperate query to find the 4.

Sorry Turlo Lomon yours gave the kind of useless syntax error that is no help...

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''items' Component, 'recipe_components_map', 'recipes', 'items'

Now...

How would I set it so that, I can update a field in the recipies acording to what recipies are found (we can drop the some of the extra items stuff becuase now that I can confirm we are getting the correct items, the update can go.

UPDATE recipes
SET race = ELF
Where (recipies use the wood elf book)

to get the recipie ID we find recipe_component_id matches to item_id's whos item name has the phrase in it.

Thanks for the help guys, it helps me understand MySQL better.

[KyrosKrane]

Sorry, Turlo, I didn't mean to sound rude. I apologize for that. I certainly do welcome feedback -- I'm not a pro at all, though I've had to learn some SQL as part of my job.

I think Turlo's hit on the solution, actually. His code didn't run because MySQL uses the back-apostrophe ` (found on the ~ key) instead of the normal apostrophe ' he used. Here's his code with the apostrophes changed.

EDIT: He also used my original code, which had the bug. I fixed that.

SELECT DISTINCT
  	Result.item_name,
  	Result.item_id,
  	recipes.recipe_id
  
  FROM
  	`items` Component, `recipe_components_map`, `recipes`, `items` Result
  
  WHERE
  	Component.item_name LIKE "%Book of Wood Elven Culture%"
  	AND Component.item_id = recipe_components_map.item_id
  	AND recipe_components_map.recipe_id = recipes.recipe_id
  	AND recipes.item_id = Result.item_id

Theoretically, that should get the same results as my query; the internal optimizations MySQL does means they effectively run the same way.

Turlo, the reason I used the left query is that I really do want to start out with every single recipe, not just those that have assocated components. (It's possible to have a recipe with no components, though that doesn't make much sense -- it's essentially an empty recipe.) The resulting join would get filtered by the WHERE clause. It's a bit messy, I admit, but I didn't want to chance missing anything because of odd recipes.

[Ngreth Thergn]

They both seem to work. Thanks