Multiple Search Fields and Subqueries? 1

[emptypixels]

I'm trying to construct a query in PHP that will be able to handle multiple search fields. The data is in multiple tables.

COMPANY
company_id
name
address

SERVICE TYPE
company_id
service

OFFICE LOCATIONS
company_id
city_name

My question is how do I construct a query that will allow there to be these three search fields (company name, service type, location).

I know that subqueries are probably needed, but I'm a newbie and still not quite clear how to get these to work properly.

The queries I've been able to construct return zero results unless all fields are filled in, or they return multiple listings of the company name since the company may have more than one office location.

I don't want the user to be required to fill in all fields to get meaningful results.

Can anyone point me in the right direction?

 

[rac2]

The solutions to optional criteria are to construct different queries in the PHP script for different searches; or write a query with an OR condition for each search field that indicates whether the field is relevant or not.

As to the multiple rows issue, that depends on what you wish to see in the results. If you wish to display different information for different options then you will probably need use different queries also.

Handle optional criteria.
Assume there is some value provided by the form which signifies that the field is optional, e.g., a blank, an empty field, or a string such as 'Any'.

SELECT * 
FROM Company C 
JOIN [Service Type} S ON S.company_id = C.company_id 
JOIN [Office Locations} L ON L.company_id = C.company_id 
WHERE 
           (C.name = @searchName OR @searchName = 'Any' )
  AND 
           (S.service = @searchService OR @searchService = 'Any' )
  AND 
           (L.city_name = @searchCity OR @searchCity = 'Any' )

To handle multiple rows when the field is not used in the search criteria you might try DISTINCT with CASE expressions.

SELECT DISTINCT 
               CASE
                   WHEN @searchName = 'Any' THEN 'Any'
                   ELSE C.name
                END AS CompanyName,
            DISTINCT 
               CASE
                   WHEN @searchService = 'Any' THEN 'Any'
                   ELSE S.service
                END AS ServiceType,
            DISTINCT 
               CASE
                   WHEN @searchCity = 'Any' THEN 'Any'
                   ELSE L.city_name
                END AS SCity
            
FROM etc

 

[r937]

well done, rac2, excellent reply, star from me

except for one thing -- you can only say DISTINCT once ;-)

r937.com | rudy.ca