Aberration SQL query

A forum for discussing Power User related features of FlyBase such as using Chado, GFF, FASTA files, etc...

Aberration SQL query

Postby Josh Goodman » Fri Aug 01, 2008 1:27 pm

This query was constructed to address this user question about aberrations.

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;
Josh Goodman
Site Admin
 
Posts: 64
Joined: Mon Nov 26, 2007 2:39 pm

Return to Power Users