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?
--
Posted via http://www.ruby-forum.com/.
On Sep 15, 4:03 pm, Aldric Giacomoni <rails-mailing-l...@andreas- s.net> wrote:> 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> Essentially.. How do I perform the ''AND'' Set operation between those > two? > -- > Posted viahttp://www.ruby-forum.com/.
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 > > FredIn 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. -- Posted via http://www.ruby-forum.com/.
On Sep 15, 4:26 pm, Aldric Giacomoni <rails-mailing-l...@andreas- s.net> wrote:> 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> 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. > -- > Posted viahttp://www.ruby-forum.com/.
Frederick Cheung wrote:> On Sep 15, 4:26�pm, Aldric Giacomoni <rails-mailing-l...@andreas- > 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. > > FredI''m about ten years too late in this company for that. Now we''re trying to bring it together. My data OCD is cringing. -- Posted via http://www.ruby-forum.com/.
Aldric Giacomoni wrote:> 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?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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.