Multi Parametered Entries - SQL Server Forums

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

Transact-SQL (2000)

Multi Parametered Entries

Author

Topic

baranozgul
Starting Member

20 Posts

Posted - 2005-01-05 : 09:23:02

Hi All,

I have an interesting problem with a query.

Suppose that you have the following table structure:

/* Tests table*/
CREATE TABLE [Tests] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TestName] [int] NOT NULL ,
) ON [PRIMARY]
GO

/*
Parameters table, contains the list of parameters each test can take
*/
CREATE TABLE [Parameters] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ParameterName] [int] NOT NULL ,
) ON [PRIMARY]
GO

/*
ParameterValues table, tells us which Test has which parameters and their values
*/
CREATE TABLE [ParametersValues] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TestId] [int] NOT NULL ,
[ParameterId] [int] NOT NULL ,
[ParameterValue] [int] NOT NULL ,
) ON [PRIMARY]
GO

Pretty simple isn't it? Below is a an example of the data stored in the tables.

Tests
id Name
1 Moisture Test
2 Speed Test
3 Heat Test

Parameters
id Name
1 Oxygen Rate
2 Average Speed
3 Test Lab
4 Tester Guy
5 Notes
6 Observed By
7 Temperature

Parameter Values
id TestId ParameterID ParameterValue
1 1 1 98%
2 1 3 Chemistry Lab
3 1 6 Dr. Stew
4 2 2 56 mph
5 2 4 Ralph
6 3 7 56 F

Now what if I would like to list the Tests, which have some certain parameter values? Such as

- Tests in which average speed is below 60 mph
- Tests in which the observer is Dr. Stew and lab is Chemistry lab.
- Tests in which Oxygen rate above 80%, observer is Dr Stew and lab is the Chemistry lab.

I find it difficult to build a performance query, considering that the required parameters may change in number, and the size of ParameterValues table may exceed some millions.

- Baran Ozgul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-05 : 10:23:55

What have you tried?

The key is to join from your tests table to the parameters table, implement your WHERE clause on the parameters table, GROUP BY the testID, and only accept results HAVING a COUNT(*) equal to the number of conditions in your WHERE clause.

The HAVING COUNT(*) = x is the key part, if you want to only return rows in which ALL conditions are satisfied. If you want rows in which ANY condition is satisfied, you wouldn't need it.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources