problem with a mysql query

[Pau Marc Muñoz Torres]

 Hello

 I am trying to execute the following SQL query at the USCS MySQL server for the database hg19

select knownGene.chrom,knownGene.exonStarts,knownGene.exonEnds, knownGene.exonCount,knownGene.strand from kgXref,knownGene, knownCanonical where kgXref. kgID=knownGene.name and kgXref.geneSymbol='CELA3A' and kgXref.kgID=knownCanonical.transcript order by knownGene.chrom asc;

The query does not retrieve any results although the CELA3A gene symbol exists at the kgXref table. 

can you tell me what is wrong? (the query is working for other genes) . How can I retrieve the same information from the USCS Browse?

thanks

Pau Marc Muñoz Torres

skype: pau_marc

http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/

[Cath Tyner]

Hello Pau Marc,

Thank you for contacting the UCSC Genome Browser support team. For hg19, it appears that the knownCanonical
table does not contain transcripts which correspond to CELA3A. To understand the results, I examined your query in smaller pieces:

1. Which UCSCIDs (transcripts) have the gene alias "CELA3A"?

mysql> select * from kgAlias where alias = "CELA3A";
+------------+--------+
| kgID       | alias  |
+------------+--------+
| uc001bfl.3 | CELA3A |
| uc009vqf.3 | CELA3A |
+------------+--------+

2. Are those two transcripts, "uc001bfl.3" and "uc009vqf.3" in knownGene? If so, what is the gene alias for each transcript?

select x.kgID,k.name,x.geneSymbol from knownGene k, kgXref x where x.kgID=k.alignID and k.name in ("uc001bfl.3","uc009vqf.3");
+------------+------------+------------+
| kgID       | name       | geneSymbol |
+------------+------------+------------+
| uc001bfl.3 | uc001bfl.3 | CELA3A     |
| uc009vqf.3 | uc009vqf.3 | CELA3B     |
+------------+------------+------------+

3. Same question as #2, for knownCanonical. There are no results for this query, showing that those transcripts are not in hg19.knownCanonical.

select x.kgID,c.transcript,x.geneSymbol from knownCanonical c, kgXref x where x.kgID=c.transcript and c.transcript in ("uc001bfl.3","uc009vqf.3");
Empty set (0.00 sec)

In hg19.knownCanonical, we can see a canonical transcript for CELA3B, but there is no canonical transcript listed for CELA3A:

select x.kgID,c.transcript,x.geneSymbol from knownCanonical c, kgXref x where x.kgID=c.transcript and x.geneSymbol in ("CELA3A","CELA3B");
+------------+------------+------------+
| kgID       | transcript | geneSymbol |
+------------+------------+------------+
| uc001bfk.3 | uc001bfk.3 | CELA3B     |
+------------+------------+------------+

​However, ​in hg38, it appears that the missing canonical has been added:

[hg38] > select x.kgID,c.transcript,x.geneSymbol from knownCanonical c, kgXref x where x.kgID=c.transcript and x.geneSymbol in ("CELA3A","CELA3B");
+------------+------------+------------+
| kgID       | transcript | geneSymbol |
+------------+------------+------------+
| uc001bfl.4 | uc001bfl.4 | CELA3A     |
| uc001bfk.4 | uc001bfk.4 | CELA3B     |
+------------+------------+------------+

Please respond to this list if you have further questions!

Thank you for contacting the UCSC Genome Browser support team. 

​Please send new and follow-up questions to one of our UCSC Genome Browser mailing lists below:

  * Post to the Public Help Forum: E

mail 

gen...@soe.ucsc.edu

​ or search the Public Archives

​  * Post to the Mirror Help Forum: Email

 genome...@soe.ucsc.edu 

or search the Mirror Archives​

​  * Confidential/private help: Email

 genom...@soe.ucsc.edu

UCSC Genome Browser Announcements List (email alerts for new data & software):

  * Subscribe: Email genome-announce+subscribe...@soe.ucsc.edu 
  * Unsubscribe: Email genome-announce+unsubscri...@soe.ucsc.edu

Join us on Social Media! Facebook, Twitter, Wordpress Blog, YouTube

​Enjoy,​

Cath
. . .

Cath Tyner

UCSC Genome Browser, Software QA & User Support

UC Santa Cruz Genomics Institute

UCSC Genome Browser

--

---
You received this message because you are subscribed to the Google Groups "UCSC Genome Browser Public Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to genome+un...@soe.ucsc.edu.
To post to this group, send email to gen...@soe.ucsc.edu.
Visit this group at https://groups.google.com/a/soe.ucsc.edu/group/genome/.
To view this discussion on the web visit https://groups.google.com/a/soe.ucsc.edu/d/msgid/genome/CADFuJLgV5D3DE%3De%3D6T2rgMEvx50wKBGuy9J0UiJ5eApSKEZ3eA%40mail.gmail.com.
For more options, visit https://groups.google.com/a/soe.ucsc.edu/d/optout.