I am using the following command to get all refseq genes from UCSC:
/usr/bin/mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu \
-A -D hg38 -e 'select concat(t.name, ".", i.version) name, \
k.locusLinkId as "EntrezId", t.chrom, t.strand, t.txStart, \
t.txEnd, t.cdsStart, t.cdsEnd, t.exonCount, t.exonStarts, \
t.exonEnds, t.score, t.name2 from refGene t join hgFixed.gbCdnaInfo i \
on t.name = i.acc join hgFixed.refLink k on t.name = k.mrnaAcc'
That returns data in the following format (showing the 1st 5 lines):
+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
| name | EntrezId | chrom | strand | txStart | txEnd | cdsStart | cdsEnd | exonCount | exonStarts | exonEnds | score | name2 |
+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
| NR_046018.2 | 100287102 | chr1 | + | 11873 | 14409 | 14409 | 14409 | 3 | 11873,12612,13220, | 12227,12721,14409, | 0 | DDX11L1 |
| NR_106918.1 | 102466751 | chr1 | - | 17368 | 17436 | 17436 | 17436 | 1 | 17368, | 17436, | 0 | MIR6859-1 |
| NR_107062.1 | 102465909 | chr1 | - | 17368 | 17436 | 17436 | 17436 | 1 | 17368, | 17436, | 0 | MIR6859-2 |
| NR_107063.1 | 102465910 | chr1 | - | 17368 | 17436 | 17436 | 17436 | 1 | 17368, | 17436, | 0 | MIR6859-3 |
| NR_128720.1 | 103504738 | chr1 | - | 17368 | 17436 | 17436 | 17436 | 1 | 17368, | 17436, | 0 | MIR6859-4 |
+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
I also want to find the accession of the canonical transcript of each of the genes returned by the command above. Those seem to be stored in the knownCanonical table:
$ /usr/bin/mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu -A -D hg38 -e 'select * from knownCanonical limit 5'
+-------+------------+-----------+-----------+------------+--------------------+
| chrom | chromStart | chromEnd | clusterId | transcript | protein |
+-------+------------+-----------+-----------+------------+--------------------+
| chrX | 100628669 | 100636806 | 1 | uc004ega.3 | ENSG00000000003.14 |
| chrX | 100584801 | 100599885 | 2 | uc004efy.5 | ENSG00000000005.5 |
| chr20 | 50934866 | 50958550 | 3 | uc002xvw.2 | ENSG00000000419.12 |
| chr1 | 169853073 | 169893959 | 4 | uc001ggs.5 | ENSG00000000457.13 |
| chr1 | 169795048 | 169854080 | 5 | uc001ggp.4 | ENSG00000000460.16 |
+-------+------------+-----------+-----------+------------+--------------------+
However, there seems to be no obvious way to link the knownCanonical table to the refGene, hgFixed.gbCdnaInfo and hgFixed.refLink tables used above.
So, how can I modify my 1st query (or, if necessary write a new one) so that my results also include the accession of the gene's canonical transcript?
