Building complex SQL query when one value depend on other value in query

Question

Database:

Table_Sensor:
    sensor_id,
    sensor_name

Table_Sensor_Detection:
    sensor_id
    azimuth (azimuth from specific sensor)

Users have the possibility to enter multiple Sensors, so I have defined Set < Integer> sensorsSet.

Users also have the possibility to enter azimuth for each sensor, so i have Sensor.azimuth, for each sensor.

All I need is logic for SQL statement that will give me all sensor_detections for entered sensors_ids, and another query for the adequate azimuth for each sensor.

This is List< String> queryList that describe logic I want accomplish.

for(Integer enteredSensorId:sensorsSet)
{
    querysList.add(
        "SELECT s FROM Table_Sensor_Detection s " +
        "WHERE s.sensor_id="+enteredSensorId +
        "AND s.azimuth"=getAzimuthForSenzor(enteredSensorId)
    );
}

The code above is simplified, so don't bother with possible mistakes. I only want to found out what is best approach to solve this problem:

a) create multiple SQL queries, run them separately and merge results (similar to code above),

b) create one SQL query (best solution if it is possible),

c) something else (what?)?

Answer 1

There are two main alternatives:

1. Multiple queries, one for each sensor. These can be executed separately, or they can be bundled together into a single overall query via the UNION ALL operator. The latter would probably be the most straightforward alternative for you.

2. However, you could also create a temporary table mapping sensor ids to desired azimuths, perform a join on that table to filter your results, and then drop the temp table. This has a certain appeal from an SQL perspective, but it's a little more esoteric. It also requires at least three separate SQL statements (four if your DB does not support temp tables with transaction-bound lifetime).

The query corresponding to alternative (1) might be along these lines:

SELECT s FROM Table_Sensor_Detection s
WHERE s.sensor_id = 1 AND s.azimuth = 10.0

UNION ALL

SELECT s FROM Table_Sensor_Detection s
WHERE s.sensor_id = 2 AND s.azimuth = 15.0

UNION ALL

-- ...

Answer 2

I solved problem with my algorithm from beginning of discussion; I create a SQL query for each sensor and azimuth for that sensor, and at the end I merge results from each query into single ArrayList.

This works, but I wonder is there a better design with single SQL query?

As I see Richard's answer doesn't solve problem because I would still need multiple queries. What I'm looking for is single advanced SQL query.

Answer 3

The simple approach is to build an IN list with two columns:

SELECT *
FROM Table_Sensor_Detection 
WHERE (sensor_id, azimuth) IN ( (1, 10.0), (2, 15.0), .... )

Given your sample code you should be able to build up that condition.

Large IN lists ("hundreds" of values) are usually quite slow however. A better way to do this is to build up a long values clause and then join that to the table:

SELECT s.*
FROM Sensor_Detection s
  JOIN (
     values (1, 10.0), (2, 15.0) 
  ) as t (id, azm)
  ON s.sensor_id = t.id AND s.azimuth = t.azm;

SQLFiddle: http://sqlfiddle.com/#!15/77c28/1

---

Unrelated but: make sure your azimuth column is defined as numeric or decimal and not as a float - otherwise you'll probably not get what you want.

Answer 4

    CREATE FUNCTION dbo.IdentifyIfInRange 
    (
        -- Add the parameters for the function here
        @Field1 float
        ,@Field2 float
        ,@low float
        ,@high float
    )
    RETURNS float
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @ResultVar float
        SET @ResultVar = NULL
        -- Add the T-SQL statements to compute the return value here
        IF @Field2 BETWEEN @low and @high
            BEGIN 
                SET @ResultVar=@Field1 
            END
        -- Return the result of the function
        RETURN @ResultVar

    END

-- Do a join:

        SELECT Table_Sensor.sensor_id
         , Table_Sensor.sensor_name
         , Table_Sensor_Detection.azimuth
         , dbo.IdentifyIfInRange( 
    Table_Sensor_Detection.azimuth || Table_Sensor.sensor_id, Table_Sensor_Detection.azimuth, low_degs, high_degs ) AS acceptable_azimuth_or_id
        FROM Table_Sensor 
         INNER JOIN Table_Sensor_Detection 
          ON Table_Sensor_Detection.sensor_id= Table_Sensor.sensor_id
        WHERE Table_Sensor.sensor_id IN ( sensor_ids )

-- OR TO Limit to only the acceptable sensorset

        SELECT Table_Sensor.sensor_id
         , Table_Sensor.sensor_name
         , Table_Sensor_Detection.azimuth
        FROM Table_Sensor 
         INNER JOIN Table_Sensor_Detection 
          ON Table_Sensor_Detection.sensor_id= Table_Sensor.sensor_id
        WHERE Table_Sensor.sensor_id IN ( sensor_ids ) and Table_Sensor.sensor_id = dbo.IdentifyIfInRange( Table_Sensor.sensor_id, Table_Sensor_Detection.azimuth, low_degs, high_degs )

Do a LEFT OUTER JOIN to get all from Table_Sensor even where Table_Sensor_Detection doesn't exist

Do a Right OUTER JOIN to get all from Table_Sensor_Detection even where Table_Sensor doesn't exist