Retrieving records from grandchild table

My question is Rails related, but even a generic SQL answer would be useful.

I'm dealing with four tab开发者_JAVA百科les: categories, books, categories_books, and recipes. Category has and belongs to many books. Books have many recipes.

Translated in Rails' code I have:

class Category < ActiveRecord::Base
  has_and_belongs_to_many :books
end

class Book < ActiveRecord::Base
  has_and_belongs_to_many :categories
  has_many :recipes
end

class Recipe < ActiveRecord::Base
  belongs_to :book
end

I'm trying to retrieve all the recipes contained in books that belong to a given category.

I know how to do this with many queries, but not with a single query. With many queries I would do:

recipes = []    
books = @category.books
books.each do |book|
  recipes << book.recipes.flatten
end

I don't like it because it requires N+1 queries. I probably need a join to do it all at once, but I'm not sure about the syntax in ActiveRecord or SQL. I'm using MySQL.

Can't help with the Ruby. A generic SQL answer (making some assumptions about column names) would be:

SELECT Recipe.*
FROM Recipe
INNER JOIN Book ON Recipe.Book = Book.Id
INNER JOIN Category ON Book.Category = Category.Id
WHERE Category.Id = ?

EDIT Added another version of the query to match Pinky Brain's new information about tables and columns. Something like this?

SELECT Recipe.*
FROM Recipe
INNER JOIN Categories_Books ON Categories_Books.book_id = Recipe.book_id
WHERE Categories_Books.category_id = ?

Assuming the following table structure...

category(
   category_id primary key
)

book(
   book_id primary key
)

recipes(
   recipe_id
  ,book_id
  ,primary key(recipe_id)  
)

categories_books(
   category_id
  ,book_id
  ,primary key(category_id, book_id)
)

...you could get what you want with the following query, which returns all receipes from all books within the specified category.

select ...
  from categories_books c
  join book             b using(book_id)
  join recipes          r using(book_id)
 where c.category_id = ?;

Note that you can drop the join to book if you don't need any data from the table, since you can join directly from categories_books to recipes via book_id. Also, make sure there is a (non-unique) index on recipes.book_id