I have 2 tables authors, and books. Then i have a habtm table authors_books. I dont have subselect capabilities in mysql, so i was wondering if rails had a builtin way to find all the books that the author did NOT write? The only way i can do this right now is to first create a temporary table and then run a second query against that. Is there a rails utility to accomplish what i want to do ? thanks adam
SELECT DISTINCT books.* FROM books LEFT JOIN author_books ON books.id = author_books.book_id WHERE author_books.book_id IS NULL AND author_books.author_id = ? -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Adam Denenberg Sent: Wednesday, 2 November 2005 11:53 AM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: [Rails] habtm no subselect I have 2 tables authors, and books. Then i have a habtm table authors_books. I dont have subselect capabilities in mysql, so i was wondering if rails had a builtin way to find all the books that the author did NOT write? The only way i can do this right now is to first create a temporary table and then run a second query against that. Is there a rails utility to accomplish what i want to do ? thanks adam _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Is there a AR way of doing this without a find_by_sql ? On 11/2/05, Wayne Robinson <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> wrote:> > SELECT DISTINCT books.* > FROM books LEFT JOIN author_books > ON books.id <http://books.id> = author_books.book_id > WHERE author_books.book_id IS NULL > AND author_books.author_id = ? > > > -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Adam Denenberg > Sent: Wednesday, 2 November 2005 11:53 AM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: [Rails] habtm no subselect > > I have 2 tables authors, and books. Then i have a habtm table > authors_books. > > I dont have subselect capabilities in mysql, so i was wondering if rails > had a builtin way to find all the books that the author did NOT write? > The only way i can do this right now is to first create a temporary > table and then run a second query against that. Is there a rails > utility to accomplish what i want to do ? > > thanks > adam > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 1, 2005, at 7:08 PM, Liquid wrote:> Is there a AR way of doing this without a find_by_sql ? > > On 11/2/05, Wayne Robinson <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> wrote: SELECT > DISTINCT books.* > FROM books LEFT JOIN author_books > ON books.id = author_books.book_id > WHERE author_books.book_id IS NULL > AND author_books.author_id = ?class Author def not_mine Book.find :all, :select => ''distinct books.*'', :joins => ''left join author_books ab on books.id=ab.book_id'', :conditions => [''ab.book_id is null and ab.author_id=?'', id] end end jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDaDaqAQHALep9HFYRAipCAJkB/imHJoUqKFrqZGUFm/6uubGRHwCgrW+2 cLxX/YE6nzPq4Lho37q3Tdo=pfgn -----END PGP SIGNATURE-----
Only by defining a specific habtm relationship specifying the find SQL query in that. ________________________________ From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Liquid Sent: Wednesday, 2 November 2005 1:08 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] habtm no subselect Is there a AR way of doing this without a find_by_sql ? On 11/2/05, Wayne Robinson <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> wrote: SELECT DISTINCT books.* FROM books LEFT JOIN author_books ON books.id = author_books.book_id WHERE author_books.book_id IS NULL AND author_books.author_id = ? -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Adam Denenberg Sent: Wednesday, 2 November 2005 11:53 AM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: [Rails] habtm no subselect I have 2 tables authors, and books. Then i have a habtm table authors_books. I dont have subselect capabilities in mysql, so i was wondering if rails had a builtin way to find all the books that the author did NOT write? The only way i can do this right now is to first create a temporary table and then run a second query against that. Is there a rails utility to accomplish what i want to do ? thanks adam _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
this doesnt work. I tested this with a sample database, created 2 authors, and 2books. I had author2 write both books and this is the result when trying to find all the books author1 didnt write: mysql> select * from books left join authors_books on books.id = authors_books.book_id ; +------+----------+-----------+---------+ | id | bookname | author_id | book_id | +------+----------+-----------+---------+ | 1 | book 1 | 1 | 1 | | 2 | book 2 | 1 | 2 | +------+----------+-----------+---------+ 2 rows in set (0.00 sec) the actual query produces no results as I expected: mysql> select distinct(id) from books left join authors_books on books.id = authors_books.book_id where authors_books.book_id IS NULL and authors_books.author_id = 1 ; Empty set (0.00 sec) i think this query is incorrect for what i need. Am I missing something here? adam Wayne Robinson wrote:> Only by defining a specific habtm relationship specifying the find SQL > query in that. > > ------------------------------------------------------------------------ > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] *On Behalf Of *Liquid > *Sent:* Wednesday, 2 November 2005 1:08 PM > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > *Subject:* Re: [Rails] habtm no subselect > > Is there a AR way of doing this without a find_by_sql ? > > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org>> wrote: > > SELECT DISTINCT books.* > FROM books LEFT JOIN author_books > ON books.id <http://books.id> = author_books.book_id > WHERE author_books.book_id IS NULL > AND author_books.author_id = ? > > > -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] On Behalf Of Adam > Denenberg > Sent: Wednesday, 2 November 2005 11:53 AM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > Subject: [Rails] habtm no subselect > > I have 2 tables authors, and books. Then i have a habtm table > authors_books. > > I dont have subselect capabilities in mysql, so i was wondering if > rails > had a builtin way to find all the books that the author did NOT write? > The only way i can do this right now is to first create a temporary > table and then run a second query against that. Is there a rails > utility to accomplish what i want to do ? > > thanks > adam > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > ------------------------------------------------------------------------ > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Hi Adam, I have had a quick look at the query you''ve used and I think the one your after could be a little different. select distinct * from books left join author_books on id author_books.book_id where author_id <> 2; This worked and showed that author with id 1 wrote the two books. On 11/3/05, Adam Denenberg <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org> wrote:> > this doesnt work. I tested this with a sample database, created 2 > authors, and 2books. I had author2 write both books and this is the > result when trying to find all the books author1 didnt write: > > mysql> select * from books left join authors_books on books.id<http://books.id>> authors_books.book_id ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 2 rows in set (0.00 sec) > > > the actual query produces no results as I expected: > > mysql> select distinct(id) from books left join authors_books on > books.id <http://books.id> = authors_books.book_id where > authors_books.book_id IS NULL > and authors_books.author_id = 1 ; > Empty set (0.00 sec) > > i think this query is incorrect for what i need. Am I missing something > here? > > adam > > Wayne Robinson wrote: > > Only by defining a specific habtm relationship specifying the find SQL > > query in that. > > > > ------------------------------------------------------------------------ > > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] *On Behalf Of *Liquid > > *Sent:* Wednesday, 2 November 2005 1:08 PM > > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > *Subject:* Re: [Rails] habtm no subselect > > > > Is there a AR way of doing this without a find_by_sql ? > > > > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org>> wrote: > > > > SELECT DISTINCT books.* > > FROM books LEFT JOIN author_books > > ON books.id <http://books.id> <http://books.id> = author_books.book_id > > WHERE author_books.book_id IS NULL > > AND author_books.author_id = ? > > > > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] On Behalf Of Adam > > Denenberg > > Sent: Wednesday, 2 November 2005 11:53 AM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > Subject: [Rails] habtm no subselect > > > > I have 2 tables authors, and books. Then i have a habtm table > > authors_books. > > > > I dont have subselect capabilities in mysql, so i was wondering if > > rails > > had a builtin way to find all the books that the author did NOT write? > > The only way i can do this right now is to first create a temporary > > table and then run a second query against that. Is there a rails > > utility to accomplish what i want to do ? > > > > thanks > > adam > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
what i am looking for actually is all the books that author2 did _NOT_ write. So the list i want is the all the distinct books that author2 did not write. Unfortunately the query your provided did not work. I had author1 and author 2 write book1, and author 2 write book 2. This should yield author2 not writing book2, however this is not the case,. mysql> select * from books left join authors_books on books.id = authors_books.book_id ; +------+----------+-----------+---------+ | id | bookname | author_id | book_id | +------+----------+-----------+---------+ | 1 | book 1 | 1 | 1 | | 1 | book 1 | 2 | 1 | | 2 | book 2 | 1 | 2 | +------+----------+-----------+---------+ 3 rows in set (0.00 sec) and your query mysql> select * from books left join authors_books on books.id = authors_books.book_id where authors_books.author_id <> 2 ; +------+----------+-----------+---------+ | id | bookname | author_id | book_id | +------+----------+-----------+---------+ | 1 | book 1 | 1 | 1 | | 2 | book 2 | 1 | 2 | +------+----------+-----------+---------+ 2 rows in set (0.00 sec) Liquid wrote:> Hi Adam, > > I have had a quick look at the query you''ve used and I think the one > your after could be a little different. > > select distinct * from books left join author_books on id = > author_books.book_id where author_id <> 2; > > This worked and showed that author with id 1 wrote the two books. > > On 11/3/05, *Adam Denenberg* <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org <mailto:adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org>> wrote: > > this doesnt work. I tested this with a sample database, created 2 > authors, and 2books. I had author2 write both books and this is the > result when trying to find all the books author1 didnt write: > > mysql> select * from books left join authors_books on books.id > <http://books.id> > authors_books.book_id ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 2 rows in set (0.00 sec) > > > the actual query produces no results as I expected: > > mysql> select distinct(id) from books left join authors_books on > books.id <http://books.id> = authors_books.book_id where > authors_books.book_id IS NULL > and authors_books.author_id = 1 ; > Empty set (0.00 sec) > > i think this query is incorrect for what i need. Am I missing > something > here? > > adam > > Wayne Robinson wrote: > > Only by defining a specific habtm relationship specifying the > find SQL > > query in that. > > > > > ------------------------------------------------------------------------ > > > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] *On Behalf Of *Liquid > > *Sent:* Wednesday, 2 November 2005 1:08 PM > > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > *Subject:* Re: [Rails] habtm no subselect > > > > Is there a AR way of doing this without a find_by_sql ? > > > > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org>>> wrote: > > > > SELECT DISTINCT books.* > > FROM books LEFT JOIN author_books > > ON books.id <http://books.id> <http://books.id> > author_books.book_id > > WHERE author_books.book_id IS NULL > > AND author_books.author_id = ? > > > > > > -----Original Message----- > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>>] On Behalf Of Adam > > Denenberg > > Sent: Wednesday, 2 November 2005 11:53 AM > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > Subject: [Rails] habtm no subselect > > > > I have 2 tables authors, and books. Then i have a habtm table > > authors_books. > > > > I dont have subselect capabilities in mysql, so i was > wondering if > > rails > > had a builtin way to find all the books that the author did > NOT write? > > The only way i can do this right now is to first create a > temporary > > table and then run a second query against that. Is there a rails > > utility to accomplish what i want to do ? > > > > thanks > > adam > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > <http://lists.rubyonrails.org/mailman/listinfo/rails> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > ------------------------------------------------------------------------ > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
doh i misread my own data, looks like this works..... thanks adam Adam Denenberg wrote:> what i am looking for actually is all the books that author2 did _NOT_ > write. So the list i want is the all the distinct books that author2 > did not write. Unfortunately the query your provided did not work. I > had author1 and author 2 write book1, and author 2 write book 2. This > should yield author2 not writing book2, however this is not the case,. > > mysql> select * from books left join authors_books on books.id = > authors_books.book_id ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 1 | book 1 | 2 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 3 rows in set (0.00 sec) > > and your query > > mysql> select * from books left join authors_books on books.id = > authors_books.book_id where authors_books.author_id <> 2 ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 2 rows in set (0.00 sec) > > Liquid wrote: > >> Hi Adam, >> >> I have had a quick look at the query you''ve used and I think the one >> your after could be a little different. >> >> select distinct * from books left join author_books on id = >> author_books.book_id where author_id <> 2; >> >> This worked and showed that author with id 1 wrote the two books. >> >> On 11/3/05, *Adam Denenberg* <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org <mailto:adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org>> >> wrote: >> >> this doesnt work. I tested this with a sample database, created 2 >> authors, and 2books. I had author2 write both books and this is the >> result when trying to find all the books author1 didnt write: >> >> mysql> select * from books left join authors_books on books.id >> <http://books.id> >> authors_books.book_id ; >> +------+----------+-----------+---------+ >> | id | bookname | author_id | book_id | >> +------+----------+-----------+---------+ >> | 1 | book 1 | 1 | 1 | >> | 2 | book 2 | 1 | 2 | >> +------+----------+-----------+---------+ >> 2 rows in set (0.00 sec) >> >> >> the actual query produces no results as I expected: >> >> mysql> select distinct(id) from books left join authors_books on >> books.id <http://books.id> = authors_books.book_id where >> authors_books.book_id IS NULL >> and authors_books.author_id = 1 ; >> Empty set (0.00 sec) >> >> i think this query is incorrect for what i need. Am I missing >> something >> here? >> >> adam >> >> Wayne Robinson wrote: >> > Only by defining a specific habtm relationship specifying the >> find SQL >> > query in that. >> > >> > >> >> ------------------------------------------------------------------------ >> >> > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] *On Behalf Of *Liquid >> > *Sent:* Wednesday, 2 November 2005 1:08 PM >> > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> > *Subject:* Re: [Rails] habtm no subselect >> > >> > Is there a AR way of doing this without a find_by_sql ? >> > >> > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org >> <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> >> > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org>>> wrote: >> > >> > SELECT DISTINCT books.* >> > FROM books LEFT JOIN author_books >> > ON books.id <http://books.id> <http://books.id> >> author_books.book_id >> > WHERE author_books.book_id IS NULL >> > AND author_books.author_id = ? >> > >> > >> > -----Original Message----- >> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> >> > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>>] On Behalf Of Adam >> > Denenberg >> > Sent: Wednesday, 2 November 2005 11:53 AM >> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> >> > Subject: [Rails] habtm no subselect >> > >> > I have 2 tables authors, and books. Then i have a habtm table >> > authors_books. >> > >> > I dont have subselect capabilities in mysql, so i was >> wondering if >> > rails >> > had a builtin way to find all the books that the author did >> NOT write? >> > The only way i can do this right now is to first create a >> temporary >> > table and then run a second query against that. Is there a >> rails >> > utility to accomplish what i want to do ? >> > >> > thanks >> > adam >> > >> > _______________________________________________ >> > Rails mailing list >> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> >> > http://lists.rubyonrails.org/mailman/listinfo/rails >> > _______________________________________________ >> > Rails mailing list >> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> >> > http://lists.rubyonrails.org/mailman/listinfo/rails >> > >> > >> > >> > >> >> ------------------------------------------------------------------------ >> > >> > _______________________________________________ >> > Rails mailing list >> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> > http://lists.rubyonrails.org/mailman/listinfo/rails >> <http://lists.rubyonrails.org/mailman/listinfo/rails> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> >> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Sorri Adam, I didn''t see that a book can be written by more than one person. I think what your after is a complex query and could either be done using a nested select or a temp table. Below I have used the nested select option. select * from books left join authors_books on books.id <http://books.id> authors_books.book_id where book_id not in ( select book_id from authors_books where author_id = 2); I hope this one does what your after... Cheers Dan On 11/3/05, Adam Denenberg <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org> wrote:> > what i am looking for actually is all the books that author2 did _NOT_ > write. So the list i want is the all the distinct books that author2 > did not write. Unfortunately the query your provided did not work. I > had author1 and author 2 write book1, and author 2 write book 2. This > should yield author2 not writing book2, however this is not the case,. > > mysql> select * from books left join authors_books on books.id<http://books.id>> authors_books.book_id ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 1 | book 1 | 2 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 3 rows in set (0.00 sec) > > and your query > > mysql> select * from books left join authors_books on books.id<http://books.id>> authors_books.book_id where authors_books.author_id <> 2 ; > +------+----------+-----------+---------+ > | id | bookname | author_id | book_id | > +------+----------+-----------+---------+ > | 1 | book 1 | 1 | 1 | > | 2 | book 2 | 1 | 2 | > +------+----------+-----------+---------+ > 2 rows in set (0.00 sec) > > Liquid wrote: > > Hi Adam, > > > > I have had a quick look at the query you''ve used and I think the one > > your after could be a little different. > > > > select distinct * from books left join author_books on id > > author_books.book_id where author_id <> 2; > > > > This worked and showed that author with id 1 wrote the two books. > > > > On 11/3/05, *Adam Denenberg* <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org <mailto:adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org>> > wrote: > > > > this doesnt work. I tested this with a sample database, created 2 > > authors, and 2books. I had author2 write both books and this is the > > result when trying to find all the books author1 didnt write: > > > > mysql> select * from books left join authors_books on books.id<http://books.id> > > <http://books.id> > > authors_books.book_id ; > > +------+----------+-----------+---------+ > > | id | bookname | author_id | book_id | > > +------+----------+-----------+---------+ > > | 1 | book 1 | 1 | 1 | > > | 2 | book 2 | 1 | 2 | > > +------+----------+-----------+---------+ > > 2 rows in set (0.00 sec) > > > > > > the actual query produces no results as I expected: > > > > mysql> select distinct(id) from books left join authors_books on > > books.id <http://books.id> <http://books.id> = authors_books.book_id > where > > authors_books.book_id IS NULL > > and authors_books.author_id = 1 ; > > Empty set (0.00 sec) > > > > i think this query is incorrect for what i need. Am I missing > > something > > here? > > > > adam > > > > Wayne Robinson wrote: > > > Only by defining a specific habtm relationship specifying the > > find SQL > > > query in that. > > > > > > > > ------------------------------------------------------------------------ > > > > > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] *On Behalf Of *Liquid > > > *Sent:* Wednesday, 2 November 2005 1:08 PM > > > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > *Subject:* Re: [Rails] habtm no subselect > > > > > > Is there a AR way of doing this without a find_by_sql ? > > > > > > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> > > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org>>> wrote: > > > > > > SELECT DISTINCT books.* > > > FROM books LEFT JOIN author_books > > > ON books.id <http://books.id> <http://books.id> <http://books.id> > > author_books.book_id > > > WHERE author_books.book_id IS NULL > > > AND author_books.author_id = ? > > > > > > > > > -----Original Message----- > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>>] On Behalf Of Adam > > > Denenberg > > > Sent: Wednesday, 2 November 2005 11:53 AM > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > Subject: [Rails] habtm no subselect > > > > > > I have 2 tables authors, and books. Then i have a habtm table > > > authors_books. > > > > > > I dont have subselect capabilities in mysql, so i was > > wondering if > > > rails > > > had a builtin way to find all the books that the author did > > NOT write? > > > The only way i can do this right now is to first create a > > temporary > > > table and then run a second query against that. Is there a rails > > > utility to accomplish what i want to do ? > > > > > > thanks > > > adam > > > > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > > > > ------------------------------------------------------------------------ > > > > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > <http://lists.rubyonrails.org/mailman/listinfo/rails> > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Just a small ammendment. The above query will give you a lot of extra information that may /may not play nicely with the book AR model. I haven''t tried it. select books.* from books left join authors_books on books.id<http://books.id>authors_books.book_id where book_id not in ( select book_id from authors_books where author_id = 2); Will only return the information from the books table. I have only tested this with MySQL 5. Cheers Dan On 11/3/05, Liquid <has.sox-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Sorri Adam, > > I didn''t see that a book can be written by more than one person. > > I think what your after is a complex query and could either be done using > a nested select or a temp table. Below I have used the nested select option. > > > select * from books left join authors_books on books.id <http://books.id>> authors_books.book_id where book_id not in ( select book_id from > authors_books where author_id = 2); > > I hope this one does what your after... > > Cheers > Dan > > On 11/3/05, Adam Denenberg <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org> wrote: > > > > what i am looking for actually is all the books that author2 did _NOT_ > > write. So the list i want is the all the distinct books that author2 > > did not write. Unfortunately the query your provided did not work. I > > had author1 and author 2 write book1, and author 2 write book 2. This > > should yield author2 not writing book2, however this is not the case,. > > > > mysql> select * from books left join authors_books on books.id<http://books.id>> > authors_books.book_id ; > > +------+----------+-----------+---------+ > > | id | bookname | author_id | book_id | > > +------+----------+-----------+---------+ > > | 1 | book 1 | 1 | 1 | > > | 1 | book 1 | 2 | 1 | > > | 2 | book 2 | 1 | 2 | > > +------+----------+-----------+---------+ > > 3 rows in set (0.00 sec) > > > > and your query > > > > mysql> select * from books left join authors_books on books.id<http://books.id>> > authors_books.book_id where authors_books.author_id <> 2 ; > > +------+----------+-----------+---------+ > > | id | bookname | author_id | book_id | > > +------+----------+-----------+---------+ > > | 1 | book 1 | 1 | 1 | > > | 2 | book 2 | 1 | 2 | > > +------+----------+-----------+---------+ > > 2 rows in set (0.00 sec) > > > > Liquid wrote: > > > Hi Adam, > > > > > > I have had a quick look at the query you''ve used and I think the one > > > your after could be a little different. > > > > > > select distinct * from books left join author_books on id > > > author_books.book_id where author_id <> 2; > > > > > > This worked and showed that author with id 1 wrote the two books. > > > > > > On 11/3/05, *Adam Denenberg* <adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org <mailto:adam-fpx97dFL/ODYtjvyW6yDsg@public.gmane.org>> > > wrote: > > > > > > this doesnt work. I tested this with a sample database, created 2 > > > authors, and 2books. I had author2 write both books and this is the > > > result when trying to find all the books author1 didnt write: > > > > > > mysql> select * from books left join authors_books on books.id<http://books.id> > > > <http://books.id> > > > authors_books.book_id ; > > > +------+----------+-----------+---------+ > > > | id | bookname | author_id | book_id | > > > +------+----------+-----------+---------+ > > > | 1 | book 1 | 1 | 1 | > > > | 2 | book 2 | 1 | 2 | > > > +------+----------+-----------+---------+ > > > 2 rows in set ( 0.00 sec) > > > > > > > > > the actual query produces no results as I expected: > > > > > > mysql> select distinct(id) from books left join authors_books on > > > books.id <http://books.id> <http://books.id> = authors_books.book_id > > where > > > authors_books.book_id IS NULL > > > and authors_books.author_id = 1 ; > > > Empty set (0.00 sec) > > > > > > i think this query is incorrect for what i need. Am I missing > > > something > > > here? > > > > > > adam > > > > > > Wayne Robinson wrote: > > > > Only by defining a specific habtm relationship specifying the > > > find SQL > > > > query in that. > > > > > > > > > > > > > ------------------------------------------------------------------------ > > > > > > > *From:* rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > > [mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>] *On Behalf Of *Liquid > > > > *Sent:* Wednesday, 2 November 2005 1:08 PM > > > > *To:* rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > > *Subject:* Re: [Rails] habtm no subselect > > > > > > > > Is there a AR way of doing this without a find_by_sql ? > > > > > > > > On 11/2/05, *Wayne Robinson* <wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org > > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org> > > > > <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org <mailto:wayner-WE50rThZ+ga6c6uEtOJ/EA@public.gmane.org >>> wrote: > > > > > > > > SELECT DISTINCT books.* > > > > FROM books LEFT JOIN author_books > > > > ON books.id <http://books.id> < http://books.id> <http://books.id> > > > author_books.book_id > > > > WHERE author_books.book_id IS NULL > > > > AND author_books.author_id = ? > > > > > > > > > > > > -----Original Message----- > > > > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > > <mailto: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>>] On Behalf Of Adam > > > > Denenberg > > > > Sent: Wednesday, 2 November 2005 11:53 AM > > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > <mailto: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > > Subject: [Rails] habtm no subselect > > > > > > > > I have 2 tables authors, and books. Then i have a habtm table > > > > authors_books. > > > > > > > > I dont have subselect capabilities in mysql, so i was > > > wondering if > > > > rails > > > > had a builtin way to find all the books that the author did > > > NOT write? > > > > The only way i can do this right now is to first create a > > > temporary > > > > table and then run a second query against that. Is there a rails > > > > utility to accomplish what i want to do ? > > > > > > > > thanks > > > > adam > > > > > > > > _______________________________________________ > > > > Rails mailing list > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > _______________________________________________ > > > > Rails mailing list > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto: Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > <mailto: Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org>> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------ > > > > > > > > _______________________________________________ > > > > Rails mailing list > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > <http://lists.rubyonrails.org/mailman/listinfo/rails> > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org <mailto:Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > > > > ------------------------------------------------------------------------ > > > > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails