I know that with GRCh38, it is possible to extract the matching RefSeq transcripts for Ensembl genes/transcripts/proteins using Biomart from Ensembl.
However, for GRCh37, this option does not exists. Using the following MySQL command returns Ensembl transcript IDs for matching RefSeq transcripts:
mysql -u anonymous -h ensembldb.ensembl.org -A -P 3337 -D homo_sapiens_core_99_37 -e "SELECT transcript.stable_id, xref.display_label FROM transcript, object_xref, xref,external_db WHERE 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 = 'RefSeq_mRNA';"
Also, I was trying to play around with the databases schema but I am not able to find any solution to problem.
Ultimately, I wish to obtain a table like the following:
Gene stable ID Transcript stable ID Protein stable ID RefSeq match transcript ENSG00000120329 ENST00000239451 ENSP00000239451 NM_031947.4 ENSG00000164219 ENST00000419445 ENSP00000404676 NM_005023.4 ENSG00000150594 ENST00000280155 ENSP00000280155 NM_000681.4 ENSG00000113578 ENST00000337706 ENSP00000338548 NM_000800.5
Thank you in advance.