# Need help with SQL **URL:** \ **Category:** Rails\ **Created:** [February 17, 2007, 8:25pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932 "2007-02-17T20:25:39Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 17, 2007, 8:25pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/1 "2007-02-17T20:25:39Z")
I am having problems writing conditions for a find request. Here is my situation: Product has and belongs to many Categories So I have join table: categories\_products I want to find all products that are not in category.id = 1. If they belong to category.id = 1 then I don’t want them returned. I have tried the following and non of them worked: Product.find(:all, :conditions =\> “categories.id != 1”, :include =\> :categories) Product.find(:all, :conditions =\> “categories\_products.category\_id != 1”, :include =\> :categories) Each of those still returned products in the category.id = 1 Any idea how I can accomplish this? Thanks for your help. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 17, 2007, 8:36pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/2 "2007-02-17T20:36:43Z")
Ben J. wrote: > I am having problems writing conditions for a find request. Here is my > situation: > > Product has and belongs to many Categories > > So I have join table: categories\_products > > I want to find all products that are not in category.id = 1. If they > belong to category.id = 1 then I don’t want them returned. > > I have tried the following and non of them worked: > > Product.find(:all, :conditions =\> “categories.id != 1”, :include =\> > :categories) > Product.find(:all, :conditions =\> “categories\_products.category\_id != > 1”, :include =\> :categories) > > Each of those still returned products in the category.id = 1 > > Any idea how I can accomplish this? > > Thanks for your help. Also, the problem is that if a product belongs to ANY other category it is returned. I want it so that if that product belongs to that category, do not return it, regardless if it’s in other categories. Thanks for your help. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 17, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/3 "2007-02-17T20:49:14Z")
> I have tried the following and non of them worked: > > Product.find(:all, :conditions =\> “categories.id != 1”, :include =\> > :categories) > Product.find(:all, :conditions =\> “categories\_products.category\_id != > 1”, :include =\> :categories) > > Each of those still returned products in the category.id = 1 > > Any idea how I can accomplish this? @products\_not\_in\_category\_1 = Product.find(:all, :include =\> :categories).map {|p| p.categories.include?(1) ? nil : p}.compact Not very efficient if you have a lot of products/categories, but that should work I think… or something along those lines. -philip ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 17, 2007, 9:31pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/4 "2007-02-17T21:31:20Z")
> > > Any idea how I can accomplish this? > > > complicated, but it needs to be SQL. > > > Then something like… select \* from products where id not in (select product\_id from categories\_products where category\_id = 1) -philip ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 18, 2007, 5:14am UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/5 "2007-02-18T05:14:54Z")
Philip H. wrote: > > > > Any idea how I can accomplish this? > > > > complicated, but it needs to be SQL. > > > > Then something like… > > select \* from products where id not in (select product\_id from > categories\_products where category\_id = 1) > > -philip thanks a lot, that worked great! ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [February 17, 2007, 8:55pm UTC](https://www.ruby-forum.com/t/need-help-with-sql/85932/6 "2007-02-17T20:55:30Z")
Philip H. wrote: > > I have tried the following and non of them worked: > > > > Product.find(:all, :conditions =\> “categories.id != 1”, :include =\> > > :categories) > > Product.find(:all, :conditions =\> “categories\_products.category\_id != > > 1”, :include =\> :categories) > > > > Each of those still returned products in the category.id = 1 > > > > Any idea how I can accomplish this? > > @products\_not\_in\_category\_1 = Product.find(:all, :include =\> > :categories).map {|p| p.categories.include?(1) ? nil : p}.compact > > Not very efficient if you have a lot of products/categories, but that > should work I think… or something along those lines. > > -philip Yeah I can’t do that because of the way I search models. It’s complicated, but it needs to be SQL.