Create SQL query with dynamic WHERE statement

Question

I'm using Postgresql as my database, in case that's helpful, although I'd like to find a pure SQL approach instead of a Postgresql specific implementation.

I have a large set of test data obtained from manufacturing a piece of electronics and I'd like to take that set of data and extract from it which units met certain criteria during test, ideally using a separate table that contains the test criteria from each step of manufacturing.

As a simple example, let's say I check the temperature readback from the unit in two different steps of the test. In step 1, the temperature should be in the range of 20C-30C while step 2 should be in the range of 50C-60C.

Let's assume the following table structure with a set of example data (table name 'test_data'):

temperature   step   serial_number
    25          1        1
    55          2        1
    19          1        2
    20          2        2

and let's assume the following table that contains the above mentioned pass criteria (table name 'criteria'):

temperature_upper   temperature_lower   step
        20                 30              1
        50                 60              2

At the moment, using a static approach, I can just use the following query:

    SELECT * FROM test_data  WHERE       
  ( test_data.step = 1 AND test_data.temperature > 20 AND test_data.temperature < 30 ) OR   
  ( test_data.step = 2 AND test_data.temperature > 50 AND test_data.temperature < 60 );

which would effectively yield the following table:

temperature   step   serial_number
    25          1        1
    55          2        1

I'd like to make my select query more dynamic and instead of begin statically defined, make it construct itself off of a list of results from the test_criteria table. The hope is to grow this into a complex query where temperature, voltage and current might be checked in step 1 but only current in step 2, for example.

Thanks for any insight!

You'd use a join between the tables, ON (test_data.step = criteria.step) AND (test_data.temperature BETWEEN criteria.temperature_lower AND criteria.temperature_upper). — Steve
– Steve, Commented Sep 14, 2019 at 18:24

ScaisEdge · Accepted Answer · 2019-09-14 19:13:26Z

1

You can solve using a join between the tables

SELECT t.*
FROM test_data  t
INNER JOIN criteria c ON t.step =  c.step 
  AND t.temperature > c.temperature_upper 
      AND t.temperature < c.temperature_lower

OR if you want >= and <=

SELECT t.*
FROM test_data  t
INNER JOIN criteria c ON t.step =  c.step 
  AND t.temperature netween c.temperature_upper AND  c.temperature_lower

answered Sep 14, 2019 at 19:13

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create SQL query with dynamic WHERE statement

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related