wood burning stoves 2.0*
The moose likes JDBC and the fly likes Select Changes Behaviour Because Of Round Brackets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select Changes Behaviour Because Of Round Brackets" Watch "Select Changes Behaviour Because Of Round Brackets" New topic
Author

Select Changes Behaviour Because Of Round Brackets

anand phulwani
Ranch Hand

Joined: Sep 10, 2005
Posts: 242
Dear Ranchers,

Here is a collection of questions for us to give a thought.The Scenarios is that
there are 7 tables and i have implemented the search through Select Like Clause.


1)Query 1st Searching '205':-
mysql> Select TripNo From TripDetails Where TripNo In(

(Select TripNo From TripDetails Where TripNo Like '%205%' OR EID Like '%205%' OR TravelDate Like '%205%' OR VoucherDate Like '%205%' OR VisitFor Like '%205%'

OR FactoryName Like '%205%' OR PoNo Like '%205%' OR BuyingCo Like '%205%' OR ItemName Like '%205%' OR IsBuyer Like '%205%' OR BuyerName Like '%205%' OR

Total_Evd_Req Like '%205%'),

(select TripNo from Transportation where TripNo Like '%205%' OR Date Like '%205%' OR Mode Like '%205%' OR Origin Like '%205%' OR Destination Like '%205%' OR

Ref Like '%205%' OR Amount Like '%205%' OR Ex Like '%205%'),

(select TripNo from Hotel where TripNo Like '%205%' OR Date Like '%205%' OR CityName Like '%205%' OR HotelName Like '%205%' OR CheckIn Like '%205%' OR

CheckOut Like '%205%' OR TotalStay Like '%205%' OR Amount Like '%205%' OR Ex Like '%205%'),

(select TripNo from Food where TripNo Like '%205%' OR Date Like '%205%' OR CityName Like '%205%' OR HotelName Like '%205%' OR BuyerName Like '%205%' OR

Amount Like '%205%' OR Ex Like '%205%'),

(select TripNo from Samples where TripNo Like '%205%' OR Date Like '%205%' OR ItemName Like '%205%' OR Purpose Like '%205%' OR Amount Like '%205%' OR Ex Like

'%205%'),

(select TripNo from Misc where TripNo Like '%205%' OR Date Like '%205%' OR Descr Like '%205%' OR Amount Like '%205%' OR Ex Like '%205%'),

(select TripNo from AdvanceRecd where TripNo Like '%205%' OR Mode Like '%205%' OR Amount Like '%205%' OR Ref Like '%205%')

);
+--------+
| TripNo |
+--------+
| 2 |
| 3 |
+--------+
2 rows in set
______________________________________________________________________________
This works fine.
Now Here The Problem starts
______________________________________________________________________________
2)Query 2 Searching '2' instead of '205':-
mysql> Select TripNo From TripDetails Where TripNo In(

(Select TripNo From TripDetails Where TripNo Like '%2%' OR EID Like '%2%' OR TravelDate Like '%2%' OR VoucherDate Like '%2%' OR VisitFor Like '%2%' OR

FactoryName Like '%2%' OR PoNo Like '%2%' OR BuyingCo Like '%2%' OR ItemName Like '%2%' OR IsBuyer Like '%2%' OR BuyerName Like '%2%' OR Total_Evd_Req Like

'%2%'),

(select TripNo from Transportation where TripNo Like '%2%' OR Date Like '%2%' OR Mode Like '%2%' OR Origin Like '%2%' OR Destination Like '%2%' OR Ref Like

'%2%' OR Amount Like '%2%' OR Ex Like '%2%'),

(select TripNo from Hotel where TripNo Like '%2%' OR Date Like '%2%' OR CityName Like '%2%' OR HotelName Like '%2%' OR CheckIn Like '%2%' OR CheckOut Like

'%2%' OR TotalStay Like '%2%' OR Amount Like '%2%' OR Ex Like '%2%'),

(select TripNo from Food where TripNo Like '%2%' OR Date Like '%2%' OR CityName Like '%2%' OR HotelName Like '%2%' OR BuyerName Like '%2%' OR Amount Like

'%2%' OR Ex Like '%2%'),

(select TripNo from Samples where TripNo Like '%2%' OR Date Like '%2%' OR ItemName Like '%2%' OR Purpose Like '%2%' OR Amount Like '%2%' OR Ex Like '%2%'),

(select TripNo from Misc where TripNo Like '%2%' OR Date Like '%2%' OR Descr Like '%2%' OR Amount Like '%2%' OR Ex Like '%2%'),

(select TripNo from AdvanceRecd where TripNo Like '%2%' OR Mode Like '%2%' OR Amount Like '%2%' OR Ref Like '%2%')

);
Query OK, -1 rows affected
______________________________________________________________________________
Whats -1 rows affected,never heard something like this sort of and whatever result is shown in 205 will be shown while searching 2 because 2 is a substring

of 205.So i thought might be problem with a numeric version tried character 'X' and was expecting a result but here is what happened.
______________________________________________________________________________
3)Query 3 Searching 'X':-

mysql> Select TripNo From TripDetails Where TripNo In(

(Select TripNo From TripDetails Where TripNo Like '%X%' OR EID Like '%X%' OR TravelDate Like '%X%' OR VoucherDate Like '%X%' OR VisitFor Like '%X%' OR

FactoryName Like '%X%' OR PoNo Like '%X%' OR BuyingCo Like '%X%' OR ItemName Like '%X%' OR IsBuyer Like '%X%' OR BuyerName Like '%X%' OR Total_Evd_Req Like

'%X%'),

(select TripNo from Transportation where TripNo Like '%X%' OR Date Like '%X%' OR Mode Like '%X%' OR Origin Like '%X%' OR Destination Like '%X%' OR Ref Like

'%X%' OR Amount Like '%X%' OR Ex Like '%X%'),

(select TripNo from Hotel where TripNo Like '%X%' OR Date Like '%X%' OR CityName Like '%X%' OR HotelName Like '%X%' OR CheckIn Like '%X%' OR CheckOut Like

'%X%' OR TotalStay Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%'),

(select TripNo from Food where TripNo Like '%X%' OR Date Like '%X%' OR CityName Like '%X%' OR HotelName Like '%X%' OR BuyerName Like '%X%' OR Amount Like

'%X%' OR Ex Like '%X%'),

(select TripNo from Samples where TripNo Like '%X%' OR Date Like '%X%' OR ItemName Like '%X%' OR Purpose Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%'),

(select TripNo from Misc where TripNo Like '%X%' OR Date Like '%X%' OR Descr Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%'),

(select TripNo from AdvanceRecd where TripNo Like '%X%' OR Mode Like '%X%' OR Amount Like '%X%' OR Ref Like '%X%')

);
Query OK, -1 rows affected
______________________________________________________________________________
This resulted into the same affect,so thought to run these queries individually
______________________________________________________________________________
4)Query 4 Searching 'X' in each table individually:-

mysql> Select TripNo From TripDetails Where TripNo Like '%X%' OR EID Like '%X%' OR TravelDate Like '%X%' OR VoucherDate Like '%X%' OR VisitFor Like '%X%' OR

FactoryName Like '%X%' OR PoNo Like '%X%' OR BuyingCo Like '%X%' OR ItemName Like '%X%' OR IsBuyer Like '%X%' OR BuyerName Like '%X%' OR Total_Evd_Req Like

'%X%';
Empty set

mysql> select TripNo from Transportation where TripNo Like '%X%' OR Date Like '%X%' OR Mode Like '%X%' OR Origin Like '%X%' OR Destination Like '%X%' OR Ref

Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%';
+--------+
| TripNo |
+--------+
| 2 |
| 2 |
| 3 |
+--------+
3 rows in set

mysql> select TripNo from Hotel where TripNo Like '%X%' OR Date Like '%X%' OR CityName Like '%X%' OR HotelName Like '%X%' OR CheckIn Like '%X%' OR CheckOut

Like '%X%' OR TotalStay Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%';
Empty set

mysql> select TripNo from Food where TripNo Like '%X%' OR Date Like '%X%' OR CityName Like '%X%' OR HotelName Like '%X%' OR BuyerName Like '%X%' OR Amount

Like '%X%' OR Ex Like '%X%';
Empty set

mysql> select TripNo from Samples where TripNo Like '%X%' OR Date Like '%X%' OR ItemName Like '%X%' OR Purpose Like '%X%' OR Amount Like '%X%' OR Ex Like

'%X%';
Empty set

mysql> select TripNo from Misc where TripNo Like '%X%' OR Date Like '%X%' OR Descr Like '%X%' OR Amount Like '%X%' OR Ex Like '%X%';
Empty set

mysql> select TripNo from AdvanceRecd where TripNo Like '%X%' OR Mode Like '%X%' OR Amount Like '%X%' OR Ref Like '%X%';
Empty set
______________________________________________________________________________
All the subqueries working properly with letter 'X' and even i checked with numeric '2' they were working great individually,i am not posting results of '2' as that would make the post more lengthier,then i thought to use only one subquery with the main query.
______________________________________________________________________________
5)Query 5 Searching 'X' with one subquery only:-

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%X%' OR EID Like '%X%' OR TravelDate Like '%X%' OR

VoucherDate Like '%X%' OR VisitFor Like '%X%' OR FactoryName Like '%X%' OR PoNo Like '%X%' OR BuyingCo Like '%X%' OR ItemName Like '%X%' OR IsBuyer Like

'%X%' OR BuyerName Like '%X%' OR Total_Evd_Req Like '%X%'));
Empty set
______________________________________________________________________________
'Voila' this works.But lets check what '2' and another character 'a' gives us
______________________________________________________________________________
6)Query 6 Searching '2' and 'a' with one subquery only:-

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%2%' OR EID Like '%2%' OR TravelDate Like '%2%' OR

VoucherDate Like '%2%' OR VisitFor Like '%2%' OR FactoryName Like '%2%' OR PoNo Like '%2%' OR BuyingCo Like '%2%' OR ItemName Like '%2%' OR IsBuyer Like

'%2%' OR BuyerName Like '%2%' OR Total_Evd_Req Like '%2%'));
Query OK, -1 rows affected

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%' OR VisitFor Like '%a%' OR FactoryName Like '%a%' OR PoNo Like '%a%' OR BuyingCo Like '%a%' OR ItemName Like '%a%' OR IsBuyer Like

'%a%' OR BuyerName Like '%a%' OR Total_Evd_Req Like '%a%'));
Query OK, -1 rows affected
______________________________________________________________________________
This is not nice,see this gives the same problem with another character,so i thought to remove some of the columns while searching while searching with 'a'.
______________________________________________________________________________
7)Query 7 Searching 'a' with one subquery only and then removing columns until a point where the query executes.:-

mysql> describe TripDetails;
+---------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| TripNo | int(11) | | PRI | 0 | |
| EID | varchar(100) | YES | | | |
| TravelDate | varchar(100) | YES | | | |
| VoucherDate | varchar(100) | YES | | | |
| VisitFor | varchar(100) | YES | | | |
| FactoryName | varchar(100) | YES | | | |
| PoNo | varchar(100) | YES | | | |
| BuyingCo | varchar(100) | YES | | | |
| ItemName | varchar(100) | YES | | | |
| IsBuyer | enum('True','False') | YES | | False | |
| BuyerName | varchar(100) | YES | | | |
| Total_Evd_Req | varchar(100) | YES | | | |
+---------------+----------------------+------+-----+---------+-------+
12 rows in set

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%' OR VisitFor Like '%a%' OR FactoryName Like '%a%' OR PoNo Like '%a%' OR BuyingCo Like '%a%' OR ItemName Like '%a%' OR IsBuyer Like

'%a%' OR BuyerName Like '%a%' OR Total_Evd_Req Like '%a%'));
Query OK, -1 rows affected

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%' OR VisitFor Like '%a%'));
Query OK, -1 rows affected

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%'));
Empty set
______________________________________________________________________________
After Reducing the columns continously at a level it worked,but this is not i wanted i want it to search with all the cloumns,finally when i was on a hit and

trial base i executed a query and you wont believe what happened,this is one of the earlier subqueries i have already executed i just removed the round

brackets and the query with one subquery worked,but how will i then add all the other queries in the subquery part.
______________________________________________________________________________
8)Query 8 Searching 'a' with one subquery only with all the columns first with dual brackets but after with one brackets:-

mysql> Select TripNo From TripDetails Where TripNo In((Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%' OR VisitFor Like '%a%' OR FactoryName Like '%a%' OR PoNo Like '%a%' OR BuyingCo Like '%a%' OR ItemName Like '%a%' OR IsBuyer Like

'%a%' OR BuyerName Like '%a%' OR Total_Evd_Req Like '%a%'));
Query OK, -1 rows affected

mysql> Select TripNo From TripDetails Where TripNo In(Select TripNo From TripDetails Where TripNo Like '%a%' OR EID Like '%a%' OR TravelDate Like '%a%' OR

VoucherDate Like '%a%' OR VisitFor Like '%a%' OR FactoryName Like '%a%' OR PoNo Like '%a%' OR BuyingCo Like '%a%' OR ItemName Like '%a%' OR IsBuyer Like

'%a%' OR BuyerName Like '%a%' OR Total_Evd_Req Like '%a%');
+--------+
| TripNo |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
3 rows in set
______________________________________________________________________________

Any solution please,i am stuck with this part,will help me a lot,
please be generous to awnser this question,
i am really looking forward to it,will be grateful,
even if a reply with any damn suggestion or any clue is given
which can be in a favour to solve this question.


Thanks and Regards, Anand
SCJP 5.0 310-055 73%, SCWCD 1.4 310-081 78%, IBM DB2 9 Fundamentals 000-730 62%
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
-1 indicates one of two things:

1) your query returned an error that you are not catching

or

2)(in the case of a SELECT query) you called mysql_affected_rows() prior to calling mysql_store_result().
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Select Changes Behaviour Because Of Round Brackets