connecting 2 tables

mistersandwiches · #1 (**permalink**) 05-10-07, 11:09

Sorry for this very simple question - I'm new to mySQL - I have 2 table and want to populate data in one table's field with a column from another. In the documentation, i see that i cannot use the ENUM type and just point it to the other table - is there another way to accomplish this?

gvee · #2 (**permalink**) 05-10-07, 11:27

Code:

INSERT INTO Table1(Column1)
SELECT Column2
FROM Table2
[WHERE ...]

I think that'll do what you want - make sure you have the same data type for the columns!

gvee · #3 (**permalink**) 05-10-07, 11:33

Note: Ok, you don't necessarily have to have columns of the same datatype, because you can convert your results before inserting them

Code:

INSERT INTO Table1(Column1)
SELECT CONVERT(decimal(7,2), Column2)
FROM Table2

HTH

mistersandwiches · #4 (**permalink**) 05-10-07, 11:39

because I'm new to mySQL - i prob didn't ask the question right - might be easier to post the code for my 2 tables and explain where the connection is:

Code:

CREATE TABLE `fonts` (
  `fontid` int(9) NOT NULL auto_increment,
  `fontname` varchar(16) collate latin1_bin NOT NULL default '',
  `foundryname` varchar(24) collate latin1_bin NOT NULL default '',
  `styles` enum('regular','roman','bold','italic') collate latin1_bin NOT NULL default 'regular',
  `enteredby` int(11) NOT NULL default '0',
  `entrydate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `editdate` date NOT NULL default '0000-00-00',
  `status` enum('entered','validated') collate latin1_bin NOT NULL default 'entered',
  `keywords` enum('sample','grunge') collate latin1_bin NOT NULL default 'sample',
  PRIMARY KEY  (`fontid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_bin

CREATE TABLE `foundry` (
  `foundryid` int(11) NOT NULL auto_increment,
  `entrydate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `editdate` date NOT NULL default '0000-00-00',
  `foundryname` varchar(24) collate latin1_bin NOT NULL default '',
  `foundryurl` varchar(24) collate latin1_bin NOT NULL default '',
  `addrline01` varchar(24) collate latin1_bin NOT NULL default '',
  `addrline02` varchar(24) collate latin1_bin NOT NULL default '',
  `city` varchar(16) collate latin1_bin NOT NULL default '',
  `state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MO','MN','MI','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') collate latin1_bin NOT NULL default 'AL',
  `zip` int(5) NOT NULL default '0',
  `country` varchar(16) collate latin1_bin NOT NULL default '',
  `phonearea` int(3) NOT NULL default '0',
  `phonenum` int(7) NOT NULL default '0',
  `contactfirst` varchar(16) collate latin1_bin NOT NULL default '',
  `contactlast` varchar(16) collate latin1_bin NOT NULL default '',
  `contacttitle` varchar(16) collate latin1_bin NOT NULL default '',
  `contactemail` varchar(24) collate latin1_bin NOT NULL default '',
  `notes` text collate latin1_bin NOT NULL,
  PRIMARY KEY  (`foundryid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_bin

goal would be to create data in table foundry, field foundryname first and then have that foundryname populate something like an ENUM foundryname field in the fonts table.

r937 · #5 (**permalink**) 05-10-07, 13:39

Quote:

Originally Posted by mistersandwiches

... i see that i cannot use the ENUM type and just point it to the other table - is there another way to accomplish this?

yes, a foreign key

healdem · #6 (**permalink**) 05-10-07, 16:39

for a foreign key constraint to work in MySQL you would need to use InnoDB or some other table type which supports foreign keys

however you don't need a foreign key constraint to be enforcred int he db IF you opt to do it programatically... it just means you have to do a bit more work to ensure the data remains consistent.

you dont need a foreign key constraint when using a join (or for that matter the old style where predicate)

Internet Services

Web Development

Digital Marketing

Consumer Tech