Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Question about narrow searching....

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-12-06 : 23:21:05
Andrew writes "I need advice to create efficient narrow search in database items.
Here is example:

Let assume that we have tables with fields:

Table USERS
USER_ID int ( Unique user identifier )
FNAME varchar(30) ( First Name )
LNAME varchar(30) ( Last Name )
PHONE varchar(30) ( Phone )

Table ORDERS
ORDER_ID int ( Unique order identifier )
USER_ID int ( User who ordered )
STREET varchar(50) ( Shipping address street )
CITY varchar(30) ( Shipping address city )
STATE varchar(2) ( Shipping address state )
ZIP varchar(10) ( Shipping address zip code )

Table ORDER_ITEMS
ORDER_ID int ( Unique order ID for this order item )
QUANTITY int ( quantity of purchased items )
PRICE float ( price single item )
ITEM_ID int ( Unique identifier of bought Item)

Table ITEMS
ITEM_ID int ( Unique identifier for item )
PRICE float ( item price )
NAME varchar(30) ( Name of item )

I would like to give the user capability to search by,
for example:

1. First and Last Name
2. Address information like Zip Code, Street, City
3. Item name

After each search I would like to use returned resultset as input
to another search. In real example it could look like this:

1. Find all user who ordered at least one time to Chicago
2. Choose from returned users only the users who last name
begins with "W"
3. Find all users in the result from query 2 who ordered item
"Soap"

How to create this search to be the most efficient?

Thanks,
Andrew"
   

- Advertisement -