• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Select Changes Behaviour Because Of Round Brackets

 
Ranch Hand
Posts: 243
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
-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().
 
reply
    Bookmark Topic Watch Topic
  • New Topic