Difference between revisions of "Seed and Plant Database"
From Poland Lab Wiki
| Line 2: | Line 2: | ||
| − | [File:Plant and seed database.png] | + | [[File:Plant and seed database.png]] |
== Example Queries == | == Example Queries == | ||
| + | |||
| + | Query to search seed inventory and return the id of the box containing the seed (i.e. the box number in the cold room.) | ||
| + | |||
| + | If a seed_id is found without a box number, this means that the seed has been harvested and not yet inventoried (or the inventory file has not been uploaded into the database yet.) | ||
| + | |||
| + | SELECT seed.seed_id, | ||
| + | seedinv.seed_id, | ||
| + | seedinv.box_id, | ||
| + | seedinv.seed_cnt, | ||
| + | seed.seed_name, | ||
| + | seed.person, | ||
| + | seed.date, | ||
| + | seed.notes, | ||
| + | seed.pedigree | ||
| + | FROM seed INNER JOIN seedinv ON seed.seed_id = seedinv.seed_id | ||
| + | WHERE seed.seed_id LIKE "%14ghs%" | ||
| + | |||
| + | Query to search the seed table. | ||
| + | |||
| + | SELECT seed.seed_id, | ||
| + | seed.seed_name, | ||
| + | seed.person, | ||
| + | seed.date, | ||
| + | seed.pedigree, | ||
| + | seed.generation, | ||
| + | seed.notes | ||
| + | FROM seed WHERE seed.seed_id LIKE "%15san%" | ||
| + | |||
| + | Query to search the plant table for specific plants. | ||
| + | |||
| + | N.B If the plant_id is not known then just looking for the plant_name or any other information that is known about the plant should return the required plant information | ||
| + | |||
| + | SELECT plant.plant_id, | ||
| + | plant.plant_name, | ||
| + | plant.source_seed_id, | ||
| + | plant.pedigree, | ||
| + | plant.person, | ||
| + | plant.date, | ||
| + | plant.notes, | ||
| + | plant.project, | ||
| + | plant.generation, | ||
| + | plant.location | ||
| + | FROM plant WHERE plant.plant_id LIKE "%14GHS%" and plant_name LIKE "%synopdh%" | ||
Latest revision as of 22:15, 11 May 2016
The main tables in the seed and plant database are shown in the figure below.
Example Queries
Query to search seed inventory and return the id of the box containing the seed (i.e. the box number in the cold room.)
If a seed_id is found without a box number, this means that the seed has been harvested and not yet inventoried (or the inventory file has not been uploaded into the database yet.)
SELECT seed.seed_id, seedinv.seed_id, seedinv.box_id, seedinv.seed_cnt, seed.seed_name, seed.person, seed.date, seed.notes, seed.pedigree FROM seed INNER JOIN seedinv ON seed.seed_id = seedinv.seed_id WHERE seed.seed_id LIKE "%14ghs%"
Query to search the seed table.
SELECT seed.seed_id, seed.seed_name, seed.person, seed.date, seed.pedigree, seed.generation, seed.notes FROM seed WHERE seed.seed_id LIKE "%15san%"
Query to search the plant table for specific plants.
N.B If the plant_id is not known then just looking for the plant_name or any other information that is known about the plant should return the required plant information
SELECT plant.plant_id, plant.plant_name, plant.source_seed_id, plant.pedigree, plant.person, plant.date, plant.notes, plant.project, plant.generation, plant.location FROM plant WHERE plant.plant_id LIKE "%14GHS%" and plant_name LIKE "%synopdh%"
