Hi all,
I am trying to retrieve chrom, chromStart, chromEnd, HUGO symbol, and strand from UCSC genome browser using a mysql query. However I am still a newbie using mysql and end up with an error about syntax. I have been using an answer posted by Pierre Lindenbaum here
My sql query looks like that:
`select distinct
C.chrom,
C.chromStart,
C.chromEnd,
X.geneSymbol
K.strand
from
knownCanonical as C,
knownGene as K,
kgXref as X
where
C.chrom=C.chrom and
C.chromStart=C.chromStart and
C.chromEnd = C.chromEnd and
X.geneSymbol=X.geneSymbol and
K.strand=K.strand
Many thanks in advance
X.geneSymbol should be X.geneSymbol,
•
written
by
Devon Ryan
Thanks Devon, but still get the same error:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`select distinct
C.chrom,
C.chromStart,
C.chromEnd,
X.geneSymbol,
K.strand
from
' at line 1
Oh, you're literally using the `s? I had assumed that those were just to mark the beginning/end of the query in your post. Get rid of those and put a semicolon (;) at the end of the command.
Just to make this explicit, the command should be:
select distinct C.chrom,C.chromStart,C.chromEnd,X.geneSymbol,K.strand from knownCanonical as C, knownGene as K, kgXref as X where C.chrom=C.chrom and C.chromStart=C.chromStart and C.chromEnd = C.chromEnd and X.geneSymbol=X.geneSymbol and K.strand=K.strand;
for mm10, you get results like:
+-------+------------+----------+---------------+--------+
| chrom | chromStart | chromEnd | geneSymbol | strand |
+-------+------------+----------+---------------+--------+
| chr1 | 3214481 | 3671498 | 0610005C13Rik | - |
| chr1 | 3648310 | 3658904 | 0610005C13Rik | - |
| chr1 | 4343506 | 4360314 | 0610005C13Rik | - |
| chr1 | 4490927 | 4497354 | 0610005C13Rik | - |
| chr1 | 4773199 | 4785726 | 0610005C13Rik | - |
| chr1 | 4807892 | 4846735 | 0610005C13Rik | - |
| chr1 | 4857693 | 4897909 | 0610005C13Rik | - |
And if the OP wants the output without the vertical lines, |, all he/she has to do is to add a -B to the query. Like this:
mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D hg19 -e 'select distinct G.chrom,G.txStart,G.txEnd,G.strand,X.geneSymbol from knownGene as G, kgXref as X where X.geneSymbol in ("LPAR1", "HIPK3", "ASXL1", "KIAA0182", "FOXN2", "ZFY", "MYO9B", "ANRIL 14-5") and X.kgId=G.name' -B
Log in to answer this question.
