Mysql select

Below is code I am using to get data from a table called trans and one called seller.  It works fine except the WHERE does not seem to be working.  I get all the rows from the trans table (about 15,000) when there are only there are only 360 with a date of '2013-04-11'. Any Ideas?  Thanks.
sargent240Asked:
Who is Participating?
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
How many rows are in your seller table that have duplicate values for the "number" field? (The field used in the join in your desired query)
0
 
sargent240Author Commented:
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";
0
 
Dave BaldwinFixer of ProblemsCommented:
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) + "' "
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Cornelia YoderArtistCommented:
"WHERE t.date like '" + reportDate + "%' "
0
 
sargent240Author Commented:
attached is the trans table.  date is a DATE column.
transTable
0
 
sargent240Author Commented:
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
0
 
Cornelia YoderArtistCommented:
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.
0
 
sargent240Author Commented:
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;
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
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.
0
 
Cornelia YoderArtistCommented:
And this query gave you a parenthesis error?

Enclose the field name date in backticks.

WHERE t.`date`='2013-04-11'
0
 
sargent240Author Commented:
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;
0
 
sargent240Author Commented:
I could post the database if that would help.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
When I remove the JOIN and ORDER it works as it should
Can you post the exact query that you used that worked?
0
 
sargent240Author Commented:
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 + "' ";
0
 
sargent240Author Commented:
s.name from the seller file obviously does not appear as we are joined to the seller table;
0
 
sargent240Author Commented:
We are NOT joined to the seller table.  Sorry.
0
 
Chris StanyonCommented:
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; 

Open 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 :)
0
 
Ray PaseurCommented:
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.
0
 
sargent240Author Commented:
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.
0
 
Chris StanyonConnect With a Mentor Commented:
Right. As I said - time to examine the data in your tables. Have a look how many rows you have in your seller table. Check that you are JOINing on the right column. For example, is your Seller ID really called number in the Seller table (seems a little odd).

The query I posted is correct, so I can only guess that the data in your tables is either not what you expect or you're selecting the wrong data.

If you want to dump your table data for both tables to a SQL file and attach we can then test with your dataset. If you're using phpMyAdmin, you'll see an Export Tab that will allow you to dump each table as a SQL file. Make sure you tick the Structure tickbox as well, and make sure the maximum length allows for a full export.

Without seeing your data, there's not much more we can do here.
0
 
sargent240Author Commented:
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";
0
 
sargent240Author Commented:
First class help from all.  Some of the best help I have had since I have been using the experts exchange.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Glad you got it working! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.