Structure query builder generated SQL statement? Multiple joins to the same table?

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)

​x
 
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

 
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

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

 
SELECT v.*FROM heats v  INNER JOIN chem c ON v.HeatSheetID = c.HeatSheetID AND c.PrimaryARS = 1 WHERE 1 = 1AND 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.

Advertisement

Answer