Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Query for Many to Many Relationship

I have the following Many to many relationship (See the picture below) in my SQL server.

Many to many relationship

In most cases there's are 2 rows in table tblWavelengths related to the table tblSensors, (in some cases only 1, and in extreme cases there can be 20 rows)

I made the following simple query to retrieve the data from those 3 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 running this query I got the following results :

Result

Here comes my problem. I want to write a query which filters only those Sensors (SensorName) which at a given moment in time (DateTimeID) has two rows (two different wavelengths) in the tblWavelengths table. So for example I want to have the results without the 77902/001 Sensor - because it has only one row (one Wavelength) related to the tblSensors at a given moment in time

like image 707
patex1987 Avatar asked Feb 01 '13 10:02

patex1987


People also ask

How do you write a SQL query for many-to-many relationships?

When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.

How do you solve a many-to-many relationship?

Many-to-many (m:n) relationships add complexity and confusion to your model and to the application development process. The key to resolve m:n relationships is to separate the two entities and create two one-to-many (1:n) relationships between them with a third intersect entity.

How do I join many-to-many tables in SQL?

In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.

What is an example of a many-to-many relationship?

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between employees and projects: employees can work on various projects, and a project can have many employees working on it.


1 Answers

You could use a windowed 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;

ADDENDUM

As an after thought I realised that you might have two results for one sensor at the same time with the same wavelength, which would return 2 records, but not have two different wavelengths. Since windowed functions don't support the use of DISTINCT an alternative is 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;
like image 91
GarethD Avatar answered Oct 20 '22 17:10

GarethD