SQL Server Query for Many, Many Relationships

Question

I have the following Many to many relationships (see the figure below) on my SQL server.

In most cases, there are 2 rows in the tblWawns table associated with the tblSensors table (in some cases only 1, and in extreme cases there may be 20 rows)

I made the following simple query to retrieve data from these three tables:

select W.DateTimeID,S.SensorName,S.SensorType,W.Channel,W.PeakNr,W.Wavelength from tblWavelengths as W Left Join tblSensorWavelengths as SW on W.tblWavelengthID = SW.WavelengthID Left Join tblSensors as S on SW.SensorID = S.SensorID order by W.DateTimeID 

After executing this query, I got the following results:

Here is my problem. I want to write a query that filters only those sensors (SensorName) that currently (DateTimeID) have two rows (two different wavelengths) in the tblWawns table. So, for example, I want to get results without the 77902/001 sensor - since it has only one line (one wavelength) associated with tblSensors at a given time

Answer 1

You can use the window function to find out the number of wavelengths for each sensorname / datetimeid combination:

 WITH Data AS ( SELECT W.DateTimeID, S.SensorName, S.SensorType, W.Channel, W.PeakNr, W.Wavelength, [Wcount] = COUNT(*) OVER(PARTITION BY s.SensorName, d.DateTimeID) from tblWavelengths as W LEFT JOIN tblSensorWavelengths as SW ON W.tblWavelengthID = SW.WavelengthID LEFT JOIN tblSensors as S ON SW.SensorID = S.SensorID ) SELECT DateTimeID, SensorName, SensorType, Channel, PeakNr, WaveLength FROM Data WHERE Wcount = 2 ORDER BY DateTimeID; 

ADDITION

As I thought, I realized that you can have two results for one sensor simultaneously with the same wavelength, which return 2 records, but do not have two different wavelengths. Since window functions do not support the use of DISTINCT , the alternative below

 WITH Data AS ( SELECT W.DateTimeID, S.SensorName, S.SensorType, W.Channel, W.PeakNr, W.Wavelength, W.tblWaveLengthID from tblWavelengths as W LEFT JOIN tblSensorWavelengths as SW ON W.tblWavelengthID = SW.WavelengthID LEFT JOIN tblSensors as S ON SW.SensorID = S.SensorID ) SELECT d.DateTimeID, d.SensorName, d.SensorType, d.Channel, d.PeakNr, d.WaveLength FROM Data d INNER JOIN ( SELECT DateTimeID, SensorName FROM Data GROUP BY DateTimeID, SensorName HAVING COUNT(DISTINCT tblWaveLengthID) = 2 ) t ON t.DateTimeID = d.DateTimeID AND t.SensorName = d.SensorName ORDER BY d.DateTimeID;