Error with SQL statement using MySQL

Posted on 2011-04-22
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

    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 142

    Expert Comment

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

    Accepted Solution

    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


    Author Comment

    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 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]

    Author Closing Comment

    Perfect, does exactly what im looking for

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now