Skip to content
Advertisement

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)

Is there a better way to structure this type of query?

–EDIT– Here’s a portion of the UI that the user is using:

enter image description here

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

enter image description here

Sample data in chem table

enter image description here

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement