Searching multiple entities at a time

[theintrepidfox]

Dear Group

Can anyone provide a sample query for the following scenario?

Let's assume I want to search for an order someone placed which might
be an individual or company. An individuals first name is stored in
column FirstName And the individuls last name in column LastName of the
contact table and the company name is stored in column CompanyName of
the company table.

If a user issues a query with CName parameter how can I search all
these columns and tables at the same time to see if there's a match? I
might add that there's a column in the orders table that holds
0=Contact 1=Company but ideally I don't want to use this column.

Thanks very much for your help and efforts.

Martin

[Stu]

Just a dirty stab at it:
DECLARE @Cname varchar(20)
SELECT NameResult = FirstName & ' ' & LastName,
Type = 'Contact'
FROM contact
WHERE FirstName LIKE '%' + @CNAME + '%'
OR LastName LIKE '%' + @CNAME + '%'
UNION ALL
SELECT NameResult = CompanyName,
Type = 'Company'
FROM company
WHERE CompanyName LIKE '%' + @CNAME + '%'

/*untested */

HTH,
Stu

[Erland Sommarskog]

(th************ @hotmail.com) writes:

Can anyone provide a sample query for the following scenario?

Let's assume I want to search for an order someone placed which might
be an individual or company. An individuals first name is stored in
column FirstName And the individuls last name in column LastName of the
contact table and the company name is stored in column CompanyName of
the company table.

If a user issues a query with CName parameter how can I search all
these columns and tables at the same time to see if there's a match? I
might add that there's a column in the orders table that holds
0=Contact 1=Company but ideally I don't want to use this column.

Since this is likely to be a commaon query, I would consider a redesign,
and have a table Customers. If you then need to have different data
for individuals and companies, you can have sub-tables to Customers
that holds this information.

The query Stu suggested will give the correct result, but it will not
perform well if there are plenty of customers. Partly this is because he
wrote:

LIKE '%' + @CNAME + '%'

Sometimes it is good to permit users to use parts within the name, but
a seacch like:

LIKE @CNAME + '%'

can make use of an index. So it is better to write the search this
way. If users need to search for parts within a name, they can always
add an initial % themselves.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp