Posted by Erland Sommarskog on 08/27/05 12:33

(theintrepidfox@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, esquel@sommarskog.se

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

[Back to original message]