bhession
asked on
Error with SQL statement using MySQL
I am attempting to use the below SQL statement. This is clearly not right but I am not sure how to correct it. Could some please show me my mistake?
SELECT * FROM details where SerialNo LIKE '%Blah%' AND ControllerSerialNo LIKE '%Blah%' AND (Address1 OR Address2 OR Address3 OR Address4 OR County LIKE '%Blah%' AND COUNTRY = 'England'
I am basically passing 4 strings from a form to a servlet, SerialNo, ControllerSerialNo, Address and Country. The problem resides in the Address part of the statement. I want to select entries that match the Address string sent to the servlet, in any of the 5 different columns in my database.
If I am unclear in my explanation please dont hesitate to ask me for more info.
SELECT * FROM details where SerialNo LIKE '%Blah%' AND ControllerSerialNo LIKE '%Blah%' AND (Address1 OR Address2 OR Address3 OR Address4 OR County LIKE '%Blah%' AND COUNTRY = 'England'
I am basically passing 4 strings from a form to a servlet, SerialNo, ControllerSerialNo, Address and Country. The problem resides in the Address part of the statement. I want to select entries that match the Address string sent to the servlet, in any of the 5 different columns in my database.
If I am unclear in my explanation please dont hesitate to ask me for more info.
I would actually suggest to go with fulltext search for that kind of search ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Kalmax I think maybe you misunderstand the query i am trying to use or create.
Four search criteria passed to servlet 1.SerialNo, 2. ControllerSerialNo, 3. Address, 4. Country
SerialNo looks for similar string in column SerialNo,
ControllerSerialNo looks for a string is in column ControllerSerialNo,
Address lookes for a string similar to it in any of these columns , Address1, Address2, Address3, Address4 or County.
Country searches for a string matching it in Country
angelll, What do you mean?
Four search criteria passed to servlet 1.SerialNo, 2. ControllerSerialNo, 3. Address, 4. Country
SerialNo looks for similar string in column SerialNo,
ControllerSerialNo looks for a string is in column ControllerSerialNo,
Address lookes for a string similar to it in any of these columns , Address1, Address2, Address3, Address4 or County.
Country searches for a string matching it in Country
angelll, What do you mean?
in regards of performance: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
ASKER
Perfect, does exactly what im looking for
SELECT * FROM details where SerialNo LIKE '%Blah%' AND ControllerSerialNo LIKE '%Blah%' AND ((Address1!="") OR (Address2!="") OR (Address3!="") OR (Address4!="")) AND (County LIKE '%Blah%' OR COUNTRY = 'England');