Xref in Ensembl

• Xrefs can be linked from the transcript or from the peptide
• To list all external identifiers for a given gene in the external DB called XXXX
  • via gene -> transcript -> object_xref -> xref -> external_db

 SELECT distinct gene_stable_id.stable_id, xref.dbprimary_acc
 FROM gene_stable_id, transcript, object_xref, xref, external_db 
 WHERE gene_stable_id.gene_id = transcript.gene_id and 
    transcript.transcript_id=object_xref.ensembl_id and 
    object_xref.ensembl_object_type="Transcript" and 
    object_xref.xref_id =xref.xref_id and 
    xref.external_db_id =external_db.external_db_id and 
    external_db.db_name="XXXX"; 

• • We go: gene -> transcript -> translation -> object_xref -> xref -> external_db

   SELECT distinct gene_stable_id.stable_id, xref.dbprimary_acc 
   FROM gene_stable_id, translation, transcript, object_xref, xref, external_db 
   WHERE gene_stable_id.gene_id = transcript.gene_id and 
      transcript.transcript_id=translation.transcript_id and 
      translation.translation_id=object_xref.ensembl_id and 
      object_xref.ensembl_object_type="Translation" and 
      object_xref.xref_id =xref.xref_id and 
      xref.external_db_id =external_db.external_db_id and 
      external_db.db_name="XXX"