Mysql select

Forgot to post the code.  Here it is.  Thanks!

       
        String command =
                "SELECT "
                + "t.sellerID, t.head, t.descrip, "
                + "ROUND(t.weight/t.head) AS AVG_WGT, "
                + "t.weight, t.price, t.date, "
                + "s.name, "
                + "t.type AS sexcode "
                + "FROM trans AS t "
                + "JOIN seller AS s ON t.sellerid = s.number "
                + "WHERE t.date='" + reportDate + "' "
                + "ORDER BY name ASC, sexcode DESC";

If 't.date' is a DATETIME column instead of just DATE, you have to make the WHERE into a range check because 'reportDate' is going to actually be '2013-04-11 00:00:00'.  It is unlikely that anything will exactly match that.  Here is pseudocode, I don't know exactly you would do it in JAVA.

"WHERE t.date>'" + reportDate + "' AND t.date<'" + (reportDate + 1day) + "' "

"WHERE t.date like '" + reportDate + "%' "

attached is the trans table.  date is a DATE column.
transTable

I tried

"WHERE t.date like '" + reportDate + "%' " 

Same result. Picked up all of the records in the trans table

Replaced where line with:

+ "WHERE t.date>'" + reportDate + "' AND t.date<'" + (reportDate + 1day) + "' " 

It will not compile as the error says

")" expected

";" expected

date is a reserved MySQL keyword.  It cannot be used as a field name.  You can try using `date` for the field name (backtick quotation marks around it).

I don't know Java syntax, but first you need to fix the use of 'date' for a field name.  In php I would use backtick marks around it.

Secondly, you need to account for the different between datetime and date field types.  If your MySQL field is in the format of  yyyy-mm-dd (date) and your input data is in the format yyyy-mm-dd hh:mm:ss (datetime), you must adjust the comparison accordingly.   Either substring off the  yyyy-mm-dd from the input data, or use the LIKE operator to compare only the first portion of the strings in MySQL.

I printed the command when I executed the program and it appears as follows.  As you can see in the file I attached in my previous posting the date format is yyyy-mm-dd.  I the command below the date is 2013-04-11.

SELECT t.sellerID, t.head, t.descrip, ROUND(t.weight/t.head) AS AVG_WGT, t.weight, t.price, t.date, s.name, t.type AS sexcode FROM trans AS t INNER JOIN seller AS s ON t.sellerid = s.number WHERE t.date='2013-04-11' ORDER BY s.name ASC, sexcode DESC;

I would say that your problem isn't Java related. Have you tried running that query directly in MySQL? I'd assume that you would get similar results. If that is the case, rather than confusing the issue by having Java in the mix too, I would just resolve the query directly in MySQL first and then move that into your java program.

And this query gave you a parenthesis error?

Enclose the field name date in backticks.

WHERE t.`date`='2013-04-11'

I have run the query in mysql and get the same results.  Below is the query I just ran directly in mysql.  I put back ticks around date, same results.  When I run it without the back ticks I get the same results.  When I remove the JOIN and ORDER it works as it should.

SELECT t.sellerID, t.head, t.descrip, ROUND(t.weight/t.head) AS AVG_WGT, t.weight, t.price, t.date, s.name, t.type AS sexcode FROM trans AS t JOIN seller AS s ON t.sellerid = s.number WHERE t.`date` = '2013-04-11' ORDER BY s.name ASC, sexcode DESC;

I could post the database if that would help.

When I remove the JOIN and ORDER it works as it should

Can you post the exact query that you used that worked?

This command works.


        String command =
                "SELECT "
                + "t.sellerID, t.head, t.descrip, "
                + "ROUND(t.weight/t.head) AS AVG_WGT, "
                + "t.weight, t.price, t.date, "

                + "t.type AS sexcode "
                + "FROM trans AS t "
                + "WHERE t.date= '" + reportDate + "' ";

s.name from the seller file obviously does not appear as we are joined to the seller table;

We are NOT joined to the seller table.  Sorry.

As it stands, there's nothing wrong with your SQL statement. Run this directly and see what you get:

SELECT t.sellerID, t.head, t.descrip, ROUND(t.weight/t.head) AS AVG_WGT, t.weight, t.price, t.date, s.name, t.type AS sexcode
FROM trans t
JOIN seller s
ON t.sellerid = s.number
WHERE t.date = '2013-04-11'
ORDER BY s.name ASC, sexcode DESC; 

Select allOpen in new window

If that doesn't give you the results you need then maybe re-examine the data in your tables. Be aware of the date format - 2013-04-11 is 11th April 2013, not 4th November - easily overlooked :)

I don't believe that date is a MySQL reserved word.  That said, it does no harm to put backticks around it, but the backticks alone would not have any effect on the query.

The format of the date column is prescribed by ISO-8601.

I tried the command from ChrisStanyon and got the same results, 74701 rows in set and when I run the command:

Select * from trans where date='2013-04-11';

I get 532 rows in set.

Bingo everyone! I looked in the seller data and number in the seller file appears for every date.  There is a date field in the seller table so I am going to have to match the date and seller number in the seller file with the sellerID and date in the trans table.  Below is the modified query that works.  Thank you very much everyone for hangin' in there and helped with the solution.

        String command =
                "SELECT "
                + "t.sellerID, t.head, t.descrip, "
                + "ROUND(t.weight/t.head) AS AVG_WGT, "
                + "t.weight, t.price, t.date, "
                + "s.name, "
->             + "s.date, "
                + "t.type AS sexcode "
                + "FROM trans AS t "
                + "JOIN seller AS s ON t.sellerid = s.number "
                + "WHERE t.date= '" + reportDate + "' "
->             + "AND s.date= '" + reportDate + "' "
                + "ORDER BY s.name ASC, sexcode DESC";

First class help from all.  Some of the best help I have had since I have been using the experts exchange.

Glad you got it working! :)