Win a copy of Java Challengers this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • paul wheaton
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Piet Souris
Bartenders:
  • salvin francis
  • Mikalai Zaikin
  • Himai Minh

SQL query doesn't return proper value

 
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I am working with the next query




I have two tables

Person
---------

Person_id , last_name, first_name, street, city

Order
--------

Order_id, Order_no, Person_id

I want ALL orders with First Name of the corresponding person (if available)

The data in the DB is

Person_id (Cod1), last_name(Isaac), first_name(Dura), street(Rozel), city(Bristol)

Order_id(1), Order_no(2), Person_id(Cod1)

When I run the query I get no errors but the column Order_id is empty

Any idea, please?

Thanks



 
author
Posts: 4276
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I recommend joining the person/order explicitly using the "INNER JOIN" syntax. It may resolve some of the issues you are seeing.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi like this


select public.order.ORDER_ID from public.person INNER JOIN public.order where public.person.person_id=public.order.person_id and public.person.first_name='Dura'

I get an an error like

Error code 0, SQL state 42601: ERROR: sintax error close to «where»

Thanks
 
Sheriff
Posts: 3036
12
Mac IntelliJ IDE Python VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm curious why your first query didn't work. Your second query though just has bad syntax. It should be:



 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I have run the new query. But I don´t get result. It is estrange. Maybe could be another reason?

Regards
 
Marshal
Posts: 26529
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It could be because what you are getting is really what is in the database, and you are mistaken about what you think is in the database. Or it could be that your code which extracts data from the database is wrong.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am running the query in the DB itself.. I mean I am not using code.

When I run select public.order.ORDER_ID from public.person,public.order where public.person.person_id=public.order.person_id

I get the number of the order but when I add the nextg two queries I get ORDER_ID but it is empty

select public.order.ORDER_ID from public.person,public.order where public.person.person_id=public.order.person_id and public.person.first_name='Dura'

or

SELECT public.order.ORDER_ID FROM public.person
INNER JOIN public.order ON public.person.person_id=public.order.person_id
WHERE public.person.first_name='Dura'

Any idea,please?

Thanks
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just created two tables person and order on my PostgreSQL database, inserted records like your person/order examples, and ran the following queries, which both returned a single row with the expected Order ID:

The query syntax is correct, so the problem must be in how it's finding (or not finding) the data. One problem could be if you have trailing spaces on your names e.g. "Dura " instead of "Dura". You can check for this by using TRIM on the name columns:

If this query works, then you know there are rogue spaces in your first_name column, so you need to trim these off when inserting/updating your data. Also, make sure you use VARCHAR (or VARCHAR2) and not CHAR as the data-type for your character columns, as some databases fill CHAR columns with spaces without you realising it.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

now when I use



It works well.

But when I type



args[0] contains Frederic

But the resultset is empty

Any idea, please?

Thanks
 
Paul Clapham
Marshal
Posts: 26529
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Of course not. If you take the string



and paste it into the tool you're using to test your SQL code, you'll find it doesn't return any records there either. That's because your table doesn't contain any records with the string "args[0]" in the first_name column.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Now I have try

String argsName=args[0];

String sqlQueryOrder = "SELECT public.order.ORDER_ID,public.person.first_name,public.person.last_name " +
"FROM public.person INNER JOIN public.order " +
"ON public.person.person_id=public.order.person_id WHERE public.person.first_name='argsName'";

Yes it doesn´´t works

Could anyone tell what way can I do it, please?

I mean this

public.person.first_name='argsName'";

Thanks
 
Sheriff
Posts: 67528
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Think!

That's no different. Does your table have a first name column with the value 'argsName'? Of course not...

The big hint: PreparedStatement
 
Skool. Stay in. Smartness. Tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic