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.