what is the error in query

hi,
I am explain now Database structure.

Database Name:- FLB
Tables :- Offence

offence table contains following fields
FirstName,LastName,Position,HR,RBI,SB,AVG,Ts.

my problem is,
I'm submitting the range values for HR,RBI,SB,AVG according to my requirment
e.g.HR- 10-50,RBI- 50-100,SB- 60-100,AVG - 0.5-1.0
and I want to display all the user for required position from table offense.

My query for 'C' position is as fallow,

query = "select * from Offense where Position='C' or OtherPos1='C' or OtherPos2='C' or OtherPos3='C' and HR between " + hrvi1 + " and " + hrvi2 + " and RBI between " + rbivi1 + " and " + rbivi2 + " and SB between " + sbvi1 + " and " + sbvi2 + " and AVG between " + avgvi1 + " and " + avgvi2 + " order by Ts DESC";


Will you please tell me,What is the mistake in this query?Because I unable to find required records.OR you may suggest new query instead of this query.


Thank,
VIkas Sawant.

re: what is the error in query

What is OtherPos1, OtherPos2 and OtherPos3? I don't see their definition. Would you be able to post the entire script.

re: what is the error in query

Are you getting any error ?

do you have the data as per your requirement in your table ?

re: what is the error in query

Thanks for reply.

Offence table contains following fields FirstName,LastName,Position,otherpos1,otherpos2,ot herpos3,HR,RBI,SB,AVG,Ts.

My query for 'C' position is as fallow,

query = "select * from Offense where Position='C' or OtherPos1='C' or OtherPos2='C' or OtherPos3='C' and HR between " + hrvi1 + " and " + hrvi2 + " and RBI between " + rbivi1 + " and " + rbivi2 + " and SB between " + sbvi1 + " and " + sbvi2 + " and AVG between " + avgvi1 + " and " + avgvi2 + " order by Ts DESC";

this query display all records for 'C' position not which follows the range values for hr,rbi,sb and avg.

This is what the problem I am facing.

Thanks
Vikas Sawant

re: what is the error in query

I'll recommend if you break this query down to a query and a subquery. Something like:

select * from
(select * from offence where HR between " + hrvi1 + " and " + hrvi2 + " and RBI between " + rbivi1 + " and " + rbivi2 + " and SB between " + sbvi1 + " and " + sbvi2 + " and AVG between " + avgvi1 + " and " + avgvi2 + ")
where Position='C' or OtherPos1='C' or OtherPos2='C' or OtherPos3='C' and order by Ts DESC.

First make sure, the innner query is generating results then focus on the outer one.

re: what is the error in query

thanks Jibran and all of you ,

This is really nice Forum.

I got the solution.I just change the way of execution of this query.

Now query is as fallow,

query = "select * from Offense where HR between "+hrvi1+" and "+hrvi2+" and RBI between "+rbivi1+" and "+rbivi2+" and SB between "+sbvi1+" and "+sbvi2+" and AVG between "+avgvi1+" and "+avgvi2+" and Position='O' or OtherPos1='O' or OtherPos2='O' or OtherPos3='O' order by Ts DESC";


Thanks,
Vikas Sawant