473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Searching multiple entities at a time


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

Aug 27 '05 #1
2 1073
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

Aug 27 '05 #2
(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****@sommarskog.se

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

Aug 27 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Matthew Burgess | last post by:
I'm not sure that XML allows me to do what I want, but here goes: Given that I have files a.xml and b.xml who both need to access an entity defined in c.ent how do I go about it? Ideally I'd...
1
by: Dalan | last post by:
I'm not certain if this is doable in the manner explained below, but surely there have been others who have done something similar. So whatever insight and assistance that can be provided will be...
1
by: Kathie G via AccessMonster.com | last post by:
Hello, I have a delima that I am not sure how to resolve. Records get entered manually or through an import. The records contain, RecordID, Client ID, Specimen Date, Visit Date, Client DOB, and...
9
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and...
33
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following...
5
by: Jeff | last post by:
We are using .Net and the wsdl Utility to generate proxies to consume web services built using the BEA toolset. The data architects on the BEA side create XML schemas with various entities in...
3
by: ian_jacobsen | last post by:
First let me start by saying that this problem is not consistently reproducible. I have a windows service that creates reports for a group of entities. This service can process multiple groups at...
60
by: Shawnk | last post by:
Some Sr. colleges and I have had an on going discussion relative to when and if C# will ever support 'true' multiple inheritance. Relevant to this, I wanted to query the C# community (the...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.