Databases
StackOverflow2013
Postsdbo
POInsertion of values into a MySQL table referencing another table for a foreign key
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInsertion of values into a MySQL table referencing another table for a foreign key
    text
    copied!<p>I've currently got the below query which I'm wanting to use to insert two strings and a foreign key. However as I'm passing the string for the foreign key I want to get the key at the time of the insertion so the record would actually read as:</p> <p>"All Asia Asset Cp", "AAA", 1</p> <p>Below is the String I use:</p> <pre><code> String sql = "INSERT into constituent " + "(constituent_name, constituent_ticker, sector_id) values (\""+ constituent.getConstituentName() + "\",\"" + constituent.getConstituentTicker() + "\"," + "(select id from sector where sector_name = \"" + constituent.getConstituentSector() + "\"))"; </code></pre> <p>Below is the query. </p> <pre><code>INSERT into constituent (constituent_name, constituent_ticker, sector_id) values ("All Asia Asset Cp","AAA", (select sector.id from sector where sector_name = "General Financial Sector")) </code></pre> <p>However, I get the below error and I'm stumped. Any ideas?</p> <pre><code>Unknown column 'sector.id' in 'field list' </code></pre> <p>Running the query as is in phpMyAdmin works. </p> <p>Running it from java using the below throws the error:</p> <pre><code> //Initialize insertValues insertValues = connect.prepareStatement(sql); //Attempt to add the values into the database try { insertValues.execute(); } catch (Exception e) { Log.e(CLASS_NAME, "createConstituent ", e); } </code></pre> <p>DDL For the sector table:</p> <pre><code>CREATE TABLE `sector` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `sector_name` varchar(100) DEFAULT NULL, `sector_url` varchar(500) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=latin1; </code></pre> <p>DDL For the constituent table:</p> <pre><code>CREATE TABLE `constituent` ( `constituent_id` int(11) NOT NULL AUTO_INCREMENT, `constituent_name` varchar(100) DEFAULT '', `constituent_ticker` varchar(10) NOT NULL, `constituent_isin_number` varchar(50) DEFAULT '', `constituent_currency` varchar(10) DEFAULT '', `sector_id` int(11) NOT NULL, PRIMARY KEY (`constituent_id`), KEY `sector_id` (`sector_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload