I have a legacy database that I can’t figure out. I’ve tried hundres of
times and spent way tooooo much time trying to figure this out. I don’t
think that rails can do it. Maybe there is a superman here who can get
this too work. I’m going to post my sql dump to produce a very simple
database. If anyone can get rails to work with this THEY WILL BE MY
HERO!!.
This is the statment I would normally use to create the join.
“select * from parent,child where parent.number = ‘2005121212121200’ and
CONCAT(SUBSTRING(child.item_number,1,14),‘00’) = parent.number”
As you can see child.item_number is links to the parent with
CONCAT(SUBSTRING(child.item_number,1,14),‘00’) = parent.number. The
last two digits of parent.number are incremented for each
child.item_number. To top it off, child has no primary because
child.item_number can have duplicates. This is a simplistic example of
an actuall database I am trying to use rails with. Are you superman? I
really hope that rails can handle this database. I would much rather use
rails than write out thousands of lines of perl and sql to handle this.
Someone please tell me this is possible!
Here is the sql to create the database with some simple information
CREATE TABLE child
(
item_number
varchar(16) NOT NULL default ‘’,
first_name
varchar(8) NOT NULL default ‘’,
last_name
varchar(8) NOT NULL default ‘’
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
–
– Dumping data for table child
INSERT INTO child
VALUES (‘2005121212121201’, ‘charlie’, ‘bowman’);
–
– Table structure for table parent
CREATE TABLE parent
(
order_number
varchar(16) NOT NULL default ‘’,
client_id
varchar(16) NOT NULL default ‘’,
submitted_by
varchar(8) NOT NULL default ‘’,
PRIMARY KEY (order_number
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
–
– Dumping data for table parent
INSERT INTO parent
VALUES (‘2005121212121200’, ‘45645465’, ‘smith’);