Error with SQL statement using MySQL

Posted on 2011-04-22
Medium Priority
Last Modified: 2013-11-24
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.
Question by:bhession
LVL 14

Expert Comment

ID: 35447323
please modify the query

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');
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35447332
I would actually suggest to go with fulltext search for that kind of search ...

Accepted Solution

Ronak Patel earned 1000 total points
ID: 35447412
Try this one:

SELECT * FROM details 
WHERE SerialNo LIKE '%Blah%' 
AND ControllerSerialNo LIKE '%Blah%' 
AND ( Address1 LIKE '%Blah%' OR Address2 LIKE '%Blah%' OR Address3 LIKE '%Blah%' OR Address4 LIKE '%Blah%' OR County LIKE '%Blah%' ) 
AND COUNTRY = 'England'

Open in new window

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Author Comment

ID: 35447437
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?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35447476

Author Closing Comment

ID: 35447600
Perfect, does exactly what im looking for

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question