All geek questions in one place

Select all rows that have at least a list of functions

I have EXPERIMENTAL_RUNS (runId), each of which has any number of SENSORS (sensorId) associated with them. With that in mind, I have an RS table to join two:

========== RS ========== runId, sensorId

Thus, if in run with runId = 1 there were sensors with a sensor Id = 1, sensorId = 6, sensorId = 8 in it, there would be 3 entries in table RS: (runId = 1, sensorId = 1) (runId = 1, sensorId = 6) (runId = 1, sensorId = 8)

Will I really return all EXPERIMENTAL_RUNS with sensors {11,13,15}? From what I read, it seems to me that I want this nested hash connection ... Will this happen?

 SELECT a.runId FROM rs a, rs b, rs c WHERE a.runId=b.runId AND b.runId=c.runId AND a.sensorId=11 AND a.sensorId=13 AND b.sensorId=15

To clarify, I want to return only EXPERIMENTAL_RUNS with sensors 11 and 13 and 15.

+1
source share
1 answer

Assuming that runId, sensorId unique in the rs table, this will find runId that has all 3 sensorId s:

 SELECT runId, COUNT(c) ct FROM rs WHERE sensorId IN (11, 13, 15) GROUP BY runId HAVING ct = 3
+3
source

Source: https://habr.com/ru/post/1487904/

More articles:


All Articles