Select Changes Behaviour Because Of Round Brackets

[anand phulwani]

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.

[Paul Campbell]

-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().