MySQL Workbench: “SELECT” is not valid at this position for this server version, expecting : '(', WITH error

33
November 08, 2019, at 2:50 PM

I have the following SQL query which I have written in MySQL Workbench:

SELECT
    t1.sample_id, t1.v_id, t1.s_type as type, t1.s_fusion, t1.e_fusion, CONCAT(t1.cpt1, ":", t1.ppt1) as point1,
    CONCAT(t1.cpt2, ":", t1.ppt2) as point2, t1.s_gene, t1.e_gene, t1.pscore as score, t2.manual_notes
FROM
(
    s_samples
    WHERE sample_id = 'S0001'
) as t1
INNER JOIN
(
    SELECT c.v_id, c.sample_id,
    CONCAT(c.sample_id,"(",e.disease,"): ",c.notes) as manual_notes
    FROM all_samples e
    LEFT JOIN s_samples c
    ON e.sample_id=c.sample_id
    WHERE c.notes is not null AND c.v_id IN
    (SELECT v_id FROM s_samples)
    AND c.sample_id = 'S0001'
) as t2
ON t1.v_id = t2.v_id
ORDER BY t1.v_id;

However, I am getting the following error: "SELECT" is not valid at this position for this server version, expecting : '(', WITH error. I am relatively new to SQL so I am not sure why I am getting this error and how to resolve this. Any insights are appreciated.

Answer 1

This is not valid SQL:

FROM
(
    s_samples
    WHERE sample_id = 'S0001'
) as t1

Perhaps you intend:

FROM (SELECT s.*
      FROM s_samples s
      WHERE s.sample_id = 'S0001'
     ) t1

Of course, this is superfluous. You can just use the table and put the filtering in an outer WHERE clause.

I think your error is caused by just some general confusion because the compiler is confused.

READ ALSO
how to insert python in to mysql

how to insert python in to mysql

I am writing an insert statement for python to add data to a mysql database and I am getting an error

19
Python mysql-connector SELECT in loop returns outdated result

Python mysql-connector SELECT in loop returns outdated result

I'm using mysql-connector to run a SELECT query in a while loop to look for new jobs

18
What is the fastest way to implementing algorithm to manipulate and insert sql records?

What is the fastest way to implementing algorithm to manipulate and insert sql records?

I have a table named as TRENDS, containing around 20k recordsI need to manipulate each row of TRENDS table based on the each column value and final output of the row is a string, named insight which is nothing but that manipulated row

25