Solved

Mysql select

Posted on 2013-11-10
23
321 Views
Last Modified: 2013-11-11
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.
0
Comment
Question by:sargent240
  • 12
  • 4
  • 3
  • +3
23 Comments
 

Author Comment

by:sargent240
ID: 39637242
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39637272
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
 
LVL 27

Expert Comment

by:yodercm
ID: 39637300
"WHERE t.date like '" + reportDate + "%' "
0
 

Author Comment

by:sargent240
ID: 39637499
attached is the trans table.  date is a DATE column.
transTable
0
 

Author Comment

by:sargent240
ID: 39637526
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
 
LVL 27

Expert Comment

by:yodercm
ID: 39637543
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
 

Author Comment

by:sargent240
ID: 39637559
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
 
LVL 35

Expert Comment

by:mccarl
ID: 39637636
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
 
LVL 27

Expert Comment

by:yodercm
ID: 39637650
And this query gave you a parenthesis error?

Enclose the field name date in backticks.

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

Author Comment

by:sargent240
ID: 39637664
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
 

Author Comment

by:sargent240
ID: 39637666
I could post the database if that would help.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 35

Expert Comment

by:mccarl
ID: 39637670
When I remove the JOIN and ORDER it works as it should
Can you post the exact query that you used that worked?
0
 

Author Comment

by:sargent240
ID: 39637724
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
 

Author Comment

by:sargent240
ID: 39637728
s.name from the seller file obviously does not appear as we are joined to the seller table;
0
 

Author Comment

by:sargent240
ID: 39637730
We are NOT joined to the seller table.  Sorry.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39638404
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39638897
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
 

Author Comment

by:sargent240
ID: 39640321
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
 
LVL 35

Accepted Solution

by:
mccarl earned 200 total points
ID: 39640342
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
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 300 total points
ID: 39640376
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
 

Author Comment

by:sargent240
ID: 39640573
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
 

Author Closing Comment

by:sargent240
ID: 39640580
First class help from all.  Some of the best help I have had since I have been using the experts exchange.
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39640628
Glad you got it working! :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now