I would like to run a query that returns various field names such as Symbol, Flybase ID, Mutagen, and Breakpoints for all records where the Feature type is 'chromosomal_deletion'
Because of the one -> many relationships between the aberration features and the mutagen and breakpoint fields it is easiest to break this query up into 2 separate queries.
This first query fetches the symbol, ID, mutagen, and the reference ID for the mutagen attribution. Note that since a single mutagen can be attributed to multiple references, the result will contain duplications.
- Code: Select all
select (select s.name from feature_synonym fs, synonym s, cvterm cvt
where cvt.name='symbol' and fs.is_current=true and fs.is_internal=false and
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
s.type_id=cvt.cvterm_id limit 1) as symbol,
f.uniquename as ID,
cvt2.name as mutagen,
p.uniquename as reference
from feature f, feature_cvterm fcvt1, cvterm cvt1, feature_cvterm fcvt2, cvterm cvt2,
cvtermprop cvtp, pub p
where cvt1.name='chromosomal_deletion' and cvtp.value='origin_of_mutation' and
f.is_obsolete=false and f.is_analysis=false and
f.feature_id=fcvt1.feature_id and fcvt1.cvterm_id=cvt1.cvterm_id and
f.feature_id=fcvt2.feature_id and fcvt2.cvterm_id=cvt2.cvterm_id and
cvt2.cvterm_id=cvtp.cvterm_id and fcvt2.pub_id=p.pub_id;
This query does a similar thing but fetches breakpoints. The same note about duplications applies as well.
- Code: Select all
select (select s.name from feature_synonym fs, synonym s, cvterm cvt
where cvt.name='symbol' and fs.is_current=true and fs.is_internal=false and
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
s.type_id=cvt.cvterm_id limit 1) as symbol,
f.uniquename as ID,
fp.value as breakpoint,
p.uniquename as reference
from feature f, feature_cvterm fcvt, cvterm cvt1, featureprop fp,
featureprop_pub fpp, pub p, cvterm cvt2
where cvt1.name='chromosomal_deletion' and f.is_analysis=false and f.is_obsolete=false and
cvt2.name in ('derived_attributed_breakpoint','non_Dmel_location') and
f.feature_id=fcvt.feature_id and fcvt.cvterm_id=cvt1.cvterm_id and
f.feature_id=fp.feature_id and fp.featureprop_id=fpp.featureprop_id and
fpp.pub_id=p.pub_id and fp.type_id=cvt2.cvterm_id;