I'm working on jsp code and have a problem with my db connection. I have two table in my database , ANN and SEC which SEC_ID is primary key and auto increment in SEC table and foreign key in ANN table. I want to use this query in my code
(SELECT
DATE,
MESSAGE
FROM ANN
WHERE ANN.SEC_ID = SEC.SEC_ID;)
But when I'm running my program , I've got this error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException
The problem is with joining the tables. You may use the following:
SELECT
A.DATE, # Assuming both DATE and MESSAGE are in ANN table
A.MESSAGE
FROM
ANN AS A
JOIN SEC AS S ON (A.SEC_ID = S.SEC_ID);
(I have used alias for performance and readability)
Remember you can also do:
SELECT
A.DATE, # Assuming both DATE and MESSAGE are in ANN table
A.MESSAGE
FROM
ANN AS A, SEC AS S
WHERE A.SEC_ID = S.SEC_ID;
But it is memory ineffient. The number of rows retrieved from database is:
Number of rows in ANN * Number of rows in SEC
That is not obviously not what you want. If you have a few thousand row in each table, then even you will get OutOfMemoryError from MySQL
The problem is just with the placement of the semicolon ';' Just check this out :
(SELECT
DATE,
MESSAGE
FROM ANN
WHERE ANN.SEC_ID = SEC.SEC_ID) ;
SELECT DATE, MESSAGE FROM ANN, SEC WHERE ANN.SEC_ID = SEC.SEC_ID;