StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMending bad BAD database design once data is in the system
text
Body
copied!<p>I know that that is not a question... erm anyway HERE is the question.</p> <p>I have inherited a database that has 1(one) table in that looks much like this. Its aim is to record what species are found in the various (200 odd) countries.</p> <pre><code>ID Species Afghanistan Albania Algeria American Samoa Andorra Angola .... Western Sahara Yemen Zambia Zimbabwe </code></pre> <p>A sample of the data would be something like this</p> <pre><code>id Species Afghanistan Albania American Samoa 1 SP1 null null null 2 SP2 1 1 null 3 SP3 null null 1 </code></pre> <p>It seems to me this is a typical many to many situation and I want 3 tables. Species, Country, and SpeciesFoundInCountry</p> <p>The link table (SpeciesFoundInCountry) would have foreign keys in both the species and Country tables.</p> <p>(It is hard to draw the diagram!)</p> <pre><code>Species SpeciesID SpeciesName Country CountryID CountryName SpeciesFoundInCountry CountryID SpeciesID </code></pre> <p>Is there a magic way I can generate an insert statement that will get the CountryID from the new Country table based on the column name and the SpeciesID where there is a 1 in the original mega table?</p> <p>I can do it for one Country (this is a select to show what I want out)</p> <pre><code>SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.Afghanistan)=1)) AND (((Country.Country)="Afghanistan")); </code></pre> <p>(the mega table is called species)</p> <p>But using this strategy I would need to do the query for each column in the original table. </p> <p>Is there a way of doing this in sql?</p> <p>I guess I can OR a load of my where clauses together and write a script to make the sql, seems inelegant though!</p> <p>Any thoughts (or clarification required)?</p>

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