# Finding unique matches between two tables

**URL:** <https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559>\
**Category:** Rails\
**Created:** [September 15, 2009, 5:03pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559 "2009-09-15T17:03:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 5:03pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/1 "2009-09-15T17:03:25Z")

</div>

This may be very simple, but I’m getting my feet tangled within Rails.  
How do I find the unique matches (same name, dob, ssn) between two  
tables?

CREATE TABLE “patientsingm”  
(“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  
“last” varchar(255),  
“first” varchar(255),  
“displayed\_name” varchar(255),  
“sex” varchar(1),  
“dob” date,  
“ssn” varchar(12),  
“account” varchar(15)  
);

CREATE TABLE “pacspatients”  
(  
“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  
“pat\_ckey” varchar(12),  
“pat\_uid” varchar(50),  
“pat\_name” varchar(255),  
“birth\_date” varchar(10),  
“sex\_code” varchar(1),  
“identifier” varchar(12)  
);

class GMPatient \< ActiveRecord::Base  
establish\_connection “gm”  
set\_table\_name “patientsingm”  
end

class SybasePatient \< ActiveRecord::Base  
establish\_connection “sqlitepacs”  
set\_table\_name “pacspatients”  
end

Essentially… How do I perform the ‘AND’ Set operation between those  
two?

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 5:18pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/2 "2009-09-15T17:18:31Z")

</div>

On Sep 15, 4:03 pm, Aldric G. \<rails-mailing-l…@andreas-  
[s.net](http://s.net)\> wrote:

> class SybasePatient \< ActiveRecord::Base  
> establish\_connection “sqlitepacs”  
> set\_table\_name “pacspatients”  
> end

If they are two separate databases then you’ll probably have to do  
this mostly in ruby (ie grab a record from one table then see if it  
exists in the other table). If both tables were in the same database  
you could probably write this as a join, but not if the tables are not  
in the same database

Fred

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 5:26pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/3 "2009-09-15T17:26:29Z")

</div>

Frederick C. wrote:

> If they are two separate databases then you’ll probably have to do  
> this mostly in ruby (ie grab a record from one table then see if it  
> exists in the other table). If both tables were in the same database  
> you could probably write this as a join, but not if the tables are not  
> in the same database
> 
> Fred

In short - there’s no clean way in Rails to do this? It’s a pure SQL  
solution?  
How do I execute pure SQL from within Rails? Do I have to use DBI?

I can definitely put both tables in the same database; my original  
choice of making it two separate databases did little besides teach me  
how to connect to more than one database with Rails 😉 This is all read  
only data anyway.

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 5:55pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/4 "2009-09-15T17:55:24Z")

</div>

On Sep 15, 4:26 pm, Aldric G. \<rails-mailing-l…@andreas-  
[s.net](http://s.net)\> wrote:

> solution?  
> How do I execute pure SQL from within Rails? Do I have to use DBI?

YourModel.connection.execute

But you can’t do it with pure sql if the tables are in different  
databases. Of course the ultimate solution to finding matches between  
two tables like this is to not need to in the first place - organise  
your data so that it isn’t duplicated.

Fred

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 5:56pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/5 "2009-09-15T17:56:43Z")

</div>

Frederick C. wrote:

> On Sep 15, 4:26�pm, Aldric G. \<rails-mailing-l…@andreas-  
> [s.net](http://s.net)\> wrote:
> 
> > solution?  
> > How do I execute pure SQL from within Rails? Do I have to use DBI?
> 
> YourModel.connection.execute
> 
> But you can’t do it with pure sql if the tables are in different  
> databases. Of course the ultimate solution to finding matches between  
> two tables like this is to not need to in the first place - organise  
> your data so that it isn’t duplicated.
> 
> Fred

I’m about ten years too late in this company for that.  
Now we’re trying to bring it together. My data OCD is cringing.

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [September 15, 2009, 6:19pm UTC](https://www.ruby-forum.com/t/finding-unique-matches-between-two-tables/175559/6 "2009-09-15T18:19:08Z")

</div>

Aldric G. wrote:

> Frederick C. wrote:
> 
> > If they are two separate databases then you’ll probably have to do  
> > this mostly in ruby (ie grab a record from one table then see if it  
> > exists in the other table). If both tables were in the same database  
> > you could probably write this as a join, but not if the tables are not  
> > in the same database
> > 
> > Fred
> 
> In short - there’s no clean way in Rails to do this?

Sure there is. Use the :joins option on find and you’ll get what you  
need.

> It’s a pure SQL  
> solution?

I don’t see why it would have to be.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid