Jeff Cohen wrote: > On Dec 9, 11:06�am, Jay Mark <[EMAIL PROTECTED]> wrote: >> @books = Books.find_by_sql ["SELECT * FROM books WHERE title IN SELECT >> title FROM authors WHERE name = ?", params[:author][:name]] >> >> One problem is where to put the () that will enclose the inner select. >> Or may be it is not needed. >> >> Thanks >> Cypray. >> -- >> Posted viahttp://www.ruby-forum.com/. > > I don't understand what tables and columns you've got here. Assuming > you have a books table that has a title column, and an authors table > with a name column, how do you relate books to authors? Is there a > foreign key that relates the tables? > > Jeff
Here is the table structure: Author has id, name, title, book_id and other columns Book has id, title, isbn, and other columns. They both have the title column. And, yes, authors has book_id as a foreign key for books. The inner query is to select title where name = the name that was passed in as the query condition. Then the outer query will select all rows where title is equal to the title returned by the inner query. The strange thing here is, title has a unique entry in authors table, but title has duplicate entries in the book table. So, the main point here is to get all rows from the book table that has the same value as the title that was returned from the authors table, which was selected based on the name that was passed in. I don't have a control over the table structure. So, changing it is not an option. Cypray -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
