database Posted August 21, 2009 Share Posted August 21, 2009 hi, i am thinking of a query for a search function that allows the user to search some columns of my database. however, the datatype are not the same as some is numeric while some is string. is this possible?i've tried this sql query below but it seems that some of it has been ignored.SELECT PersonDetails.OID, Address.Unit, PersonDetails.FirstName, PersonDetails.LastName, PersonDetails.ContactNo From Address, PersonDetails WHERE Address.Unit = 'input' OR PersonDetails.FirstName LIKE 'input%' OR PersonDetails.LastName LIKE 'input%' OR PersonDetails.OID = input AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'Active'The part (AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'Active') is ignored and everyone seems to live in unit 1A when the 'input' is 1A. Also, those who are not active are being displayed together in the same unit. If 'input' is s or l, it will displayed those whose firstname or lastname contains s or l. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 21, 2009 Share Posted August 21, 2009 That query is only going to work if input is a number, not if it's a string. That's because of this:PersonDetails.OID = inputThere's no quotes around that, so if it's a string and not a number that's an error. The part (AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'Active') is ignoredPut parentheses around your AND and OR conditions, the AND is not getting checked because one of the ORs matches. Link to comment Share on other sites More sharing options...
database Posted August 24, 2009 Author Share Posted August 24, 2009 That query is only going to work if input is a number, not if it's a string. That's because of this:PersonDetails.OID = inputThere's no quotes around that, so if it's a string and not a number that's an error.Put parentheses around your AND and OR conditions, the AND is not getting checked because one of the ORs matches.Thanks alot justsomeguy,Parenthesis was what i needed. i've placed my and query in the front & or at the back after taking a look at the or operator tutorial in w3schools.As for the numeric & string, i guess i will have to do 2 seperate query for that. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.