CODEWITHSUNDEEP
javamysql
rumpydumpy
rumpydumpy

Reputation: 13

Query in Java - Subquery gone wrong or what?

I'm having a problem with this query of mine. I've spent almost an hour trying to correct it but still getting an error.

Heres my code:

sql = "INSERT INTO tbl_case \n" +
                    "(Case_ID, Employee_ID, Patient_ID, Chief_Complaint, Date) \n" +
                    "VALUES \n" +
                    "(\n" +
                    "   '',\n" +
                    "   'EMP0001',\n" +
                    "   '(SELECT Patient_ID from tbl_patient WHERE ID_no = '"+getPatient_ID()+"')',\n" +
                    "   '"+txtcc.getText()+"',\n" +
                    "   '"+time+"'\n" +
                    ")";
                dp.sop("Query 'Create Case': "+sql);
                dp.Insertion(sql);

Note: dp stands for a class I inherited the methods from. dp.Selection is a simple executeQuery I made for retrieving data. dp.Insertion is for updating.

Here is the output of the query in String:

Query 'Create Case': INSERT INTO tbl_case 
(Case_ID, Employee_ID, Patient_ID, Chief_Complaint, Date) 
VALUES 
(
    '',
    'EMP0001',
    '(SELECT Patient_ID from tbl_patient WHERE ID_no = '10000201117')',
    'Head Ache',
    '2016-01-30 09:55:27'
)

and the error is a mysql syntax error near:

'10000201117)',
    'Head Ache',
    '2016-01-30 10:07:08'
)' at Line 7

anyone spotted whats wrong? I'm using mysql from xampp.

Upvotes: 0

Views: 45

Answers (2)

Titus
Titus

Reputation: 22474

I don't think you need to surround the SELECT statement with quotes. As it is now, this part '(SELECT Patient_ID from tbl_patient WHERE ID_no = ' is interpreted as a value instead of part of a query.

Try: (SELECT ...) instead of '(SELECT ...)'

Upvotes: 1

Vishal Ashar
Vishal Ashar

Reputation: 42

Since (SELECT Patient_ID from tbl_patient WHERE ID_no = '10000201117') is in single quotes you might want to try putting 10000201117 in double quotes.

For example:

'(SELECT Patient_ID from tbl_patient WHERE ID_no = "10000201117")'

Upvotes: 1

Related Questions