-- DDH 040301

-- SQL queries necessary to construct Vickies GrainGenes Marker Report
-- see Report_design_04.xls

-- Gene : Lr34 (Triticum)

-- Gene : Gene Class
select
geneclass.id as "Gene Class ID",
geneclass.name as "Gene Class"
from gene
inner join geneclass on geneclass.id = gene.geneclassid
where gene.id = 1461;

-- Gene : Other_name
select
genesynonym.name as "Other name",
reference.title as "Reference"
from genesynonym
inner join reference on genesynonym.referenceid = reference.id
where genesynonym.geneid = 1461
and genesynonym.type = "Other_name";

-- Gene : Go Term

-- Gene : Information Source
select
reference.id as "Information Source ID",
reference.title as "Information Source"
from geneinfosource
inner join reference on geneinfosource.referenceid = reference.id
where geneinfosource.geneid = 1461;

-- Gene : Comment
select
generemark.remark as "Comment"
from generemark
where generemark.geneid = 1461
and generemark.type = "Comment";

-- Gene : Numbered References
select
reference.id as "Reference ID",
genewgcreference.number as "Number",
reference.title as "Reference"
from genewgcreference
inner join reference on genewgcreference.referenceid = reference.id
where genewgcreference.geneid = 1461;

-- Gene : Allele
select
allele.id as "Allele ID",
allele.name as "Allele"
from allelegene
inner join allele on allelegene.alleleid = allele.id
where allelegene.geneid = 1461;

-- Gene : Data Curated
select
colleague.id as "Data Curator ID",
colleague.name as "Data Curator",
genedatacurator.date as "Date"
from genedatacurator
inner join colleague on genedatacurator.colleagueid = colleague.id
where genedatacurator.geneid = 1461;

-- Gene : Locus
select
locus.id as "Locus ID",
locus.name as "Locus"
from genelocus
inner join locus on genelocus.locusid = locus.id
where genelocus.geneid = 1461;

-- Gene : Locus (rest goes in Locus report)

-- Gene : QTL
select
qtl.id as "QTL ID",
qtl.name as "QTL"
from qtlassociatedgene
inner join qtl on qtlassociatedgene.qtlid = qtl.id
where qtlassociatedgene.geneid = 1461;

-- Gene : Mapping (goes in Locus report)
-- Gene : Probe (goes in Locus report)

-- Gene : Pathology
select
pathology.id as "Pathology ID",
pathology.name as "Pathology"
from gene
inner join pathology on gene.pathologyid = pathology.id
where gene.id = 1461;

-- Gene : Germplasm
select
germplasm.id as "Germplasm ID",
genegermplasm.type as "Germplasm Type",
germplasm.name as "Germplasm"
from genegermplasm
inner join germplasm on genegermplasm.germplasmid = germplasm.id
where genegermplasm.geneid = 1461;

-- Gene : Image
select
image.name as "Image"
from geneimage
inner join image on geneimage.imageid = image.id
where geneimage.geneid = 1461;