• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query doesn't return proper value

 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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



 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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.
 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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
 
Greg Charles
Sheriff
Posts: 2985
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • 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:



 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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
 
Paul Clapham
Sheriff
Posts: 20990
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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.
 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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.
 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 20990
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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.
 
Isaac Ferguson
Ranch Hand
Posts: 839
1
  • Mark post as helpful
  • send pies
  • 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
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64720
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic