Here’s the use case for this:
Get me a list of heats where element #18 is between 95 and 100 AND element #2 is between 0.0 and 0.5.
This works just fine (not sure it’s the best SQL/approach, but it works).
The question/potential problem is that the number of element/range conditions is variable. The user is constructing a query to find records that meet a variety of element/range conditions.
SELECT v.* FROM heats v INNER JOIN chem c ON v.HeatSheetID = c.HeatSheetID AND c.PrimaryARS = 1 LEFT JOIN ElementValues ev ON c.AnalysisNo = ev.AnalysisNo LEFT JOIN ElementValues ev1 ON c.AnalysisNo = ev1.AnalysisNo WHERE (ev.ElementID = 18 AND ev.ElementValue BETWEEN 95 AND 100) AND (ev1.ElementID = 2 AND ev1.ElementValue BETWEEN 0.0 AND 0.5)
Is there a better way to structure this type of query?
–EDIT– Here’s a portion of the UI that the user is using:
Each ‘heat’ record has up to 25 elements associated with it (these are stored in the elementvalue table).
CREATE TABLE [dbo].[ElementValues]( [AnalysisNo] [bigint] NOT NULL, [ElementID] [smallint] NOT NULL, [ElementValue] [decimal](7, 4) NULL CREATE TABLE [dbo].[chem]( [AnalysisNo] [bigint] NOT NULL, [AnalysisDateTime] [datetime] NULL, [Quality] [nvarchar](50) NULL, [DTImported] [datetime] NULL, [HeatSheetID] [int] NULL, [PrimaryARS] [bit] NULL, [RecStatus] [bit] NULL
Sample Data in ElementValues
Sample data in chem table
Advertisement
Answer
It does really depend on how you are building the query…. I would probably use exists
and you can easily add a new exists
condition matched to the users selections.
SELECT v.* FROM heats v INNER JOIN chem c ON v.HeatSheetID = c.HeatSheetID AND c.PrimaryARS = 1 WHERE 1 = 1 AND EXISTS (SELECT 1 FROM ElementValues ev WHERE c.AnalysisNo = ev.AnalysisNo AND ev.ElementID = 18 AND ev.ElementValue BETWEEN 95 AND 100) AND EXISTS (SELECT 1 FROM ElementValues ev WHERE c.AnalysisNo = ev.AnalysisNo AND ev.ElementID = 2 AND ev.ElementValue BETWEEN 0.0 AND 0.5) -- ...
I imagine it will perform pretty much the same as what you have, but it should be a touch easier to build the query from the user input.