Answers.Body: <p>You can link them with the <code>kgXref</code> table, since <code>kgXref.refseq == refGene.name</code> and <code>kgXref.kgID == knownCanonical.transcript</code>. Since it seems that <code>knownCanonical.transcript</code> is what you want anyway, you don't even need to join on it:</p>
  
  <pre><code>mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu \
  -A -D hg38 -e 'select concat(t.name, ".", i.version) name, x.kgID, \
  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, hgFixed.refLink k, kgXref x) \
  on (t.name = i.acc and t.name = k.mrnaAcc and t.name = x.refseq) limit 10'
  </code></pre>
  
  <p>The output is then:</p>
  
  <pre><code>+-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  | name        | kgID       | EntrezId  | chrom | strand | txStart | txEnd  | cdsStart | cdsEnd | exonCount | exonStarts         | exonEnds           | score | name2      |
  +-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  | NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-1  |
  | NR_107062.1 | uc031tlm.1 | 102465909 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-2  |
  | NR_107063.1 | uc032cta.1 | 102465910 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-3  |
  | NR_128720.1 | uc032dmn.1 | 103504738 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-4  |
  | NR_036051.1 | uc031tlb.1 | 100302278 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-2  |
  | NR_036266.1 | uc033cjs.1 | 100422831 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-9  |
  | NR_036267.1 | uc032csz.1 | 100422834 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-10 |
  | NR_036268.1 | uc032hiw.1 | 100422919 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-11 |
  | NR_026822.1 | uc001aak.4 |    654835 | chr1  | -      |   34610 |  36081 |    36081 |  36081 |         3 | 34610,35276,35720, | 35174,35481,36081, |     0 | FAM138C    |
  | NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |  187890 | 187958 |   187958 | 187958 |         1 | 187890,            | 187958,            |     0 | MIR6859-1  |
  +-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  </code></pre>
  
  <p>If you want the protein ID then join <code>knownCanonical</code> on <code>x.kgID</code>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-07T21:02:46.693Z
Answers.LastActivityDate: 2017-08-07T21:02:46.693Z
Answers.UserDisplayName: Devon Ryan
Body: <p>I am using the following command to get all refseq genes from UCSC:</p>
  
  <pre><code>/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'
  </code></pre>
  
  <p>That returns data in the following format (showing the 1st 5 lines):</p>
  
  <pre><code>+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
  | 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 |
  +-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
  </code></pre>
  
  <p>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 <code>knownCanonical</code> table:</p>
  
  <pre><code>$ /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 |
  +-------+------------+-----------+-----------+------------+--------------------+
  </code></pre>
  
  <p>However, there seems to be no obvious way to link the <code>knownCanonical</code> table to the <code>refGene</code>, <code>hgFixed.gbCdnaInfo</code> and <code>hgFixed.refLink</code> tables used above. </p>
  
  <p>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? </p>
  
Tags: <public-databases><identifiers><ucsc>
Title: Get canonical transcript from UCSC
ThreadText: # Get canonical transcript from UCSC
  
  (Asked by: username_0 on 2017-08-07)
  
  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?
  
  **Tags:** public-databases, identifiers, ucsc
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-08-07. Score: 3 | Accepted answer
  
  You can link them with the `kgXref` table, since `kgXref.refseq == refGene.name` and `kgXref.kgID == knownCanonical.transcript`. Since it seems that `knownCanonical.transcript` is what you want anyway, you don't even need to join on it:
  
  ```
  mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu \
  -A -D hg38 -e 'select concat(t.name, ".", i.version) name, x.kgID, \
  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, hgFixed.refLink k, kgXref x) \
  on (t.name = i.acc and t.name = k.mrnaAcc and t.name = x.refseq) limit 10'
  
  ```
  
  The output is then:
  
  ```
  +-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  | name        | kgID       | EntrezId  | chrom | strand | txStart | txEnd  | cdsStart | cdsEnd | exonCount | exonStarts         | exonEnds           | score | name2      |
  +-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  | NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-1  |
  | NR_107062.1 | uc031tlm.1 | 102465909 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-2  |
  | NR_107063.1 | uc032cta.1 | 102465910 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-3  |
  | NR_128720.1 | uc032dmn.1 | 103504738 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-4  |
  | NR_036051.1 | uc031tlb.1 | 100302278 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-2  |
  | NR_036266.1 | uc033cjs.1 | 100422831 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-9  |
  | NR_036267.1 | uc032csz.1 | 100422834 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-10 |
  | NR_036268.1 | uc032hiw.1 | 100422919 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-11 |
  | NR_026822.1 | uc001aak.4 |    654835 | chr1  | -      |   34610 |  36081 |    36081 |  36081 |         3 | 34610,35276,35720, | 35174,35481,36081, |     0 | FAM138C    |
  | NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |  187890 | 187958 |   187958 | 187958 |         1 | 187890,            | 187958,            |     0 | MIR6859-1  |
  +-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
  
  ```
  
  If you want the protein ID then join `knownCanonical` on `x.kgID`.
  
  
  ---
  
  