# How to find rows where no row in associated table exists?

**URL:** <https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332>\
**Category:** Rails\
**Created:** [April 6, 2007, 10:32pm UTC](https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332 "2007-04-06T22:32:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stubbsuk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e56c9b/32.png) [@stubbsuk](https://www.ruby-forum.com/u/stubbsuk)\
**Post date:** [April 6, 2007, 10:32pm UTC](https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332/1 "2007-04-06T22:32:26Z")

</div>

Basically, how do I do this “The Rails Way™”

SELECT \* FROM beta\_users u LEFT JOIN beta\_codes c ON u.id =  
c.beta\_user\_id AND c.code IS NULL

At the moment I’m using find\_by\_sql, and that works Ok, but what would  
be the Rails way?

---

<div class="post-metadata">

**Author:** ![stubbsuk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e56c9b/32.png) [@stubbsuk](https://www.ruby-forum.com/u/stubbsuk)\
**Post date:** [April 6, 2007, 10:38pm UTC](https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332/2 "2007-04-06T22:38:27Z")

</div>

users = BetaCode.find(:all, :conditions =\> “code is  
null”).collect(&:beta\_user)

This is assuming you have the following table definitions:  
class BetaUser \< ActiveRecord::Base  
has\_(one | many) :beta\_code(s)  
end  
class BetaCode \< ActiveRecord::Base  
belongs\_to :beta\_user  
end

What this does is find all beta\_codes whose code is null, and then  
iterates  
over them and finds their user… it would be similar to writing

null\_codes = BetaCode.find(:all, :conditions =\> “code is null”)  
users = []  
null\_codes.each do |code|  
users \<\< code.beta\_user  
end

---

<div class="post-metadata">

**Author:** ![stubbsuk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e56c9b/32.png) [@stubbsuk](https://www.ruby-forum.com/u/stubbsuk)\
**Post date:** [April 7, 2007, 5:48pm UTC](https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332/3 "2007-04-07T17:48:59Z")

</div>

On Apr 6, 9:37 pm, “Luke I.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> What this does is find all beta\_codes whose code is null, and then iterates  
> over them and finds their user… it would be similar to writing
> 
> null\_codes = BetaCode.find(:all, :conditions =\> “code is null”)  
> users = []  
> null\_codes.each do |code|  
> users \<\< code.beta\_user  
> end

That wont work if there are no rows in the beta\_codes table, which is  
the situation I’m in. I show a list of users who havn’t been sent an  
invite code, and they only get an entry in that table when the invite  
has been sent.

---

<div class="post-metadata">

**Author:** ![stubbsuk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e56c9b/32.png) [@stubbsuk](https://www.ruby-forum.com/u/stubbsuk)\
**Post date:** [April 9, 2007, 6:07pm UTC](https://www.ruby-forum.com/t/how-to-find-rows-where-no-row-in-associated-table-exists/91332/4 "2007-04-09T18:07:12Z")

</div>

maybe:

@users = BetaUser.find :all, :include =\> “beta\_codes”, :conditions =\>  
“beta\_codes.code IS NULL”

assuming that you have the association set up correctly:

BetaUser has\_many:beta\_code (or has\_one)  
BetaCode belongs\_to :beta\_user

On 7 Apr., 17:47, “Stuart Grimshaw” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)