# AR - find all records not already associated **URL:** \ **Category:** Rails\ **Created:** [January 18, 2010, 7:25pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814 "2010-01-18T19:25:29Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![byrnejb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/d2c977/32.png) [@byrnejb](https://www.ruby-forum.com/u/byrnejb)\ **Post date:** [January 18, 2010, 7:25pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814/1 "2010-01-18T19:25:29Z")
Given A has\_many :B :through =\> :C A has\_many :C B has\_many :A :through =\> :C B has\_many :C C belongs\_to :A C belongs\_to :B What is the idiomatic way to find all B that are not associated with a given value for A? This will work but is there a simpler way? this\_a = A.find\_by\_attribute(‘value’) all\_b = B.find(:all) assigned = this\_a.bs unassigned = all\_b - assigned ---
**Author:** ![byrnejb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/d2c977/32.png) [@byrnejb](https://www.ruby-forum.com/u/byrnejb)\ **Post date:** [January 18, 2010, 7:28pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814/2 "2010-01-18T19:28:25Z")
Let me rephrase the question. How would one construct an AR find so as to return the desired subset of B records in a single SQL query? ---
**Author:** ![byrnejb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/d2c977/32.png) [@byrnejb](https://www.ruby-forum.com/u/byrnejb)\ **Post date:** [January 18, 2010, 7:42pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814/3 "2010-01-18T19:42:33Z")
James B. wrote: > Given > > A has\_many :B :through =\> :C > A has\_many :C > > B has\_many :A :through =\> :C > B has\_many :C > > C belongs\_to :A > C belongs\_to :B > > What is the idiomatic way to find all B that are not associated with a > given value for A? This will work but is there a simpler way? > > this\_a = A.find\_by\_attribute(‘value’) > all\_b = B.find(:all) > assigned = this\_a.bs > unassigned = all\_b - assigned Here’s sample SQL – you can work out the find! SELECT \* from b LEFT JOIN c on (c.b\_id = b.id) LEFT JOIN a on (c.a\_id = a.id) WHERE a.id != 17 # or whatever value Best, –Â Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![byrnejb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/d2c977/32.png) [@byrnejb](https://www.ruby-forum.com/u/byrnejb)\ **Post date:** [January 18, 2010, 8:02pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814/4 "2010-01-18T20:02:57Z")
On Jan 18, 1:42Â pm, Marnen Laibow-Koser [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Here’s sample SQL – you can work out the find! Thanks. I will have a go at it. ---
**Author:** ![byrnejb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/d2c977/32.png) [@byrnejb](https://www.ruby-forum.com/u/byrnejb)\ **Post date:** [January 18, 2010, 11:21pm UTC](https://www.ruby-forum.com/t/ar-find-all-records-not-already-associated/181814/5 "2010-01-18T23:21:58Z")
On Jan 18, 6:42Â pm, Marnen Laibow-Koser [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > C belongs\_to :B > > SELECT \* from b LEFT JOIN c on (c.b\_id = b.id) LEFT JOIN a on (c.a\_id = > a.id) > WHERE a.id != 17 # or whatever value I don’t think that’s quite right - if the c table contains a\_id b\_id 1 2 17 2 3 2 Then clearly the b with id 2 is associated with the a with id 17, but I believe the above query would return the b with id 2 (twice) something like select \* from b left join c on c.b\_id = b.id and c.a\_id = 17 left join a on c.a\_id = a.id where a.id is null should do the trick (and if you have foreign key constraints then you don’t ever need to join the a table Fred