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.
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" |
|
|
|
|