Finding unique matches between two tables

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?

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?

class GMPatient < ActiveRecord::Base
establish_connection "gm"
set_table_name "patientsingm"
end

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

Frederick Cheung 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 :wink: This is all read
only data anyway.

Frederick Cheung 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?

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

Frederick Cheung wrote: