SQL query doesn't return proper value

[Angus Ferguson]

Hi

I am working with the next query

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' 

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]

I recommend joining the person/order explicitly using the "INNER JOIN" syntax. It may resolve some of the issues you are seeing.

[Angus Ferguson]

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]

I'm curious why your first query didn't work. Your second query though just has bad syntax. It should be:

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'

[Angus Ferguson]

Hi

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

Regards

[Paul Clapham]

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]

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]

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:

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' ;

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'  ;

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:

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

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]

Hi

now when I use

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='Frederic'";

It works well.

But when I type

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='args[0]'";

args[0] contains Frederic

But the resultset is empty

Any idea, please?

Thanks

[Paul Clapham]

Of course not. If you take the string

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='args[0]'

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]

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]

Think!

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

The big hint: PreparedStatement