CODEWITHSUNDEEP
mysql
brucezepplin
brucezepplin

Reputation: 9772

Mysql query working when executed on server side, but not client

****Solution***

I had to wrap query in double quotes instead of single quotes.

If I log into the following mysql server:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A

and then run the following query:

select distinct 
    kgID, genesymbol, refseq 
from 
    kgXref 
where 
    genesymbol = 'GLRA1') a 
inner join 
    (select * from knownGene) b on a.kgID = b.name 
inner join 
    (select distinct 
         name, transcript, chromStart, chromEnd, 
         substr(peptides, 1, 1) as ref_pep,
         substr(peptides, 3, 1) as mut_pep 
     from 
         snp137CodingDbSnp) c on a.refseq = c.transcript

I get my desired results.

However if I run this in the following way:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A hg19 -D hg19 -e 'select distinct c.name,c.transcript from  (select distinct kgID,genesymbol,refseq from kgXref where genesymbol = 'GLRA1') a inner join (select * from knownGene) b on a.kgID = b.name inner join (SELECT distinct name, transcript,chromStart,chromEnd, substr(peptides,1,1) as ref_pep,substr(peptides,3,1) as mut_pep FROM snp137CodingDbSnp) c on a.refseq = c.transcript'

I get the following error

ERROR 1054 (42S22) at line 1: Unknown column 'GLRA1' in 'where clause'

where GLRA1 is not a column, but rather an id within the column geneSymbol.

Why is it that the query works when I am logged into the server, but not when I run it in the second way?

Upvotes: 0

Views: 39

Answers (1)

GoBusto
GoBusto

Reputation: 4788

Simple - you're closing the "quote" too early:

-e 'select distinct c.name,c.transcript from  (select distinct kgID,genesymbol,refseq from kgXref where genesymbol = 'GLRA1')

The ' before GLRA1 will close the opening quote just before the select keyword.

Upvotes: 1

Related Questions