Help with writing SQL code: same functionality as Yell.com

Question

Can anyone help me with the trying to write SQL (MS SqlServer) - I must admit this is not by best skill.

What I want to do is exactly the same functionality as appears for the seach boxes for the Yell website i.e.

• Search for company type

• AND/OR company name

• AND/OR enter a company name

• in a Location

if anyone can suggest the SQL code you would need to write in order to get the same fuctionality as Yell - that would be great.

Answer 1

Can you provide the database layout (schema) that the sql would run against? It would be necessary to give you an exact result.

But generally speaking what you are looking for is

SELECT * FROM tablename WHERE companyType = 'type' OR companyName = 'companyName'

Answer 2

What you need first is not SQL code, but a database design. Only then does it make any sense to start writing SQL.

A simple table schema that matches Yell's functionality might be something like:

CREATE TABLE Company (
    company_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    company_name VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL
)

and then you'd search for it by name with SQL like:

SELECT * FROM Company WHERE company_name like '%text%'

or by location like:

SELECT * FROM Company WHERE location = 'Location'

Of course, a real-world location search would have to use either exact city and state, or a zip code lookup, or some intelligent combination thereof. And a real table would then have lots more fields, like descriptions, etc. But that's the basic idea.

Answer 3

Typically, one does something like this:

-- All these are NULL unless provided
DECLARE @CompanyType AS varchar
DECLARE @CompanyName AS varchar
DECLARE @Town AS varchar

SELECT *
FROM TABLE_NAME
WHERE (@CompanyType IS NULL OR COMPANY_TYPE_COLUMN LIKE '%' + @CompanyType  + '%')
    AND (@CompanyName IS NULL OR COMPANY_NAME_COLUMN LIKE '%' + @CompanyName + '%')
    AND (@Town IS NULL OR TOWN_COLUMN LIKE '%' + @Town + '%')

Or this (only match start of columns with the wildcards):

-- All these are NULL unless provided
DECLARE @CompanyType AS varchar
DECLARE @CompanyName AS varchar
DECLARE @Town AS varchar

SELECT *
FROM TABLE_NAME
WHERE (@CompanyType IS NULL OR COMPANY_TYPE_COLUMN LIKE @CompanyType  + '%')
    AND (@CompanyName IS NULL OR COMPANY_NAME_COLUMN LIKE @CompanyName + '%')
    AND (@Town IS NULL OR TOWN_COLUMN LIKE @Town + '%')