Solved

Returning results from search page using SQL Server 2000

Posted on 2011-09-07
4
246 Views
Last Modified: 2012-05-12
Hi.  I am trying to figure out a way to return results to users searching for data in an Access 2010 frontend (created in Access 2003) that has a SQL Server 2000 backend.  The backend is a data warehouse that will eventually have lots of data.  Right now there are only about 4,000 master records and 40,000 detail records.

Users will be selecting materials from a dropdown and then I need to return SampleIDs that use the materials selected.

Below is a small sample of how the data looks.  The real tables have many more rows per SampleID and many materials.  But, like the sample data, there are 2 different material fields (used for different purposes in the database) that I need to look at in each row for the purposes of this search project.

MasterID is the key in the first table and DetailID is the key in the second table.  The two tables are related by MasterID.  

Here are two examples:  
1. A user selects materials AA and DD from the list (they can select up to 4 materials).   I need to return sampleID 6-123 as the only sampleid that uses those materials.

2. A user selects material WW from the list.  I need to return sampleIDs 4-234 and 6-123 since they both use WW.

I wrote SQL code to take all the materials in the normalized detail table and concatenate all the materials (possibly 2 per row but with many rows) and put all that data as one line per SampleID in a pre-processed table.

For example:
SampleID      MaterialsList
4-234            AA,XX,WW,QQ,DD,TT
6-123            EE,FF,AA,BB,WW,XX,QQ

Then I could do a “like” in the search but the program to create the data above takes a while to run from SQL Agent.  When I run it in the Query Analyzer, takes less than 40 seconds but from the SQL Server Agent with a stored procedure, it isn’t even finished after 5 minutes.  So the first problem using the SP to create stored data I can search from is that I would have to run this process on a regular basis and the data would never be current to the moment unless the process just ran.  The second problems is that using a ”like” operator means the process to return search results may be slow.  We will eventually be upgrading to a later version of SQL Server and a much faster server but, for now, this is what we have to work with.

Then I thought that there may be a way to get this logic to run without using pre-processed data.   I am working on that now.  I tried using a select with a subselect to get the materials but that won’t work because multiple materials are returned from the subselect.

I hope my explanation makes sense.  Does anyone have any suggestions?

Thanks,
Alexis
 Sample data
0
Comment
Question by:alexisbr
  • 3
4 Comments
 

Author Comment

by:alexisbr
Comment Utility
Here's the subquery I wrote for this test data:
select sampleid, (select material1 from MaterialTestDetail b where b.masterid = a.masterid)
from MaterialTestMaster a

I get the error "subquery returned more than 1 value" since more than one material1 value exists.  If I could somehow do this select and be able to concatenate the multiple results, including the values in material2, into one line, that might work.  I would then have to compare the material(s) the user selected against this list and return the sampleid or multiple sampleids.

Thanks,
Alexis
0
 
LVL 21

Accepted Solution

by:
huslayer earned 500 total points
Comment Utility
that will help you get more about concatenate string values.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

sorry don't have access to 2000 to help you with an actual query...
0
 

Author Comment

by:alexisbr
Comment Utility
huslyer,
Thanks for the link.  I got pulled into a last minute project after I posted this question and have not had a chance to review it yet.  I should be able to get back to this today.

Alexis
0
 

Author Closing Comment

by:alexisbr
Comment Utility
Thanks for the great links.  This should help resolve my questions.  I will post another question if I need more help.
Alexis
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now