SQL Server Query for Many to Many Relationship

SQL Server Query for Many to Many Relationship

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;

SQL How do I query a many-to-many relationship

Assuming these tables:

  • Posts: id, author, date, content
  • Tags: id, name
  • PostTags: post_id, tag_id

The last table is often called a join table and facilitates a many-to-many relationship between Posts and Tags.

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'sql'

Basically, think of a many-to-many relationship as two one-to-many relationships, because that's how they're implemented in normal RDBMSs. So the above query has a one-to-many join from Posts to PostTags and another from Tags to PostTags.

The PostTags table I created has a composite primary key, being (post_id, tag_id). That combination will be unique. Many disfavour composite keys so you'll often see people creating a primary key column:

  • PostTags: id, post_id, tag_id

Either method is fine. It's largely a philosophical difference.

Update: if you want to select all the posts that have a particular tag and all the tags those posts have then:

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE p.id IN
(SELECT post_id
FROM PostTags pt
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'xyz')

Another way to do this is:

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE EXISTS
(SELECT post_id
FROM PostTags pt
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'xyz'
AND pt.post_id = p.id)

Which performs better will need to be tested and may vary depending on database vendor and version. A good optimizer (ie Oracle) will probably optimize them to perform the same. Others may not.

Now this will get you rows back like this:

Post 1, tag 1
Post 1, tag 2
Post 3, tag 2
Post 3, tag 3

so you'll need to combine them, preferably in application logic rather than SQL. Some RDBMSs have vendor-specific extensions for this kind of thing, like MySQL's GROUP_CONCAT() function.

T-SQL - Query many-to-many relation table with order

You will need to start by inserting your input into a table with the Rank and StudentId. I came up with a way you can do this without having to parse csv. It uses the CHARINDEX of the StudentIds in the input. Note that if you have an input value which is not in your table it will be ignored. Of course you could just parse the csv instead, up to you.

After you have the input in a table all you need to do is join the input to your table to get only records that match the input. Then GROUP BY SubjectId and take the SubjectIds that have the same count as the records in your input and the same count as records with that SubjectId in your table.

All together the answer will look like this. (For some reason (*) would not save, so I did (* )).

DECLARE @InputTable AS TABLE (
[Rank] INT IDENTITY(1,1),
StudentId VARCHAR(1) )

INSERT INTO @InputTable (StudentId)
SELECT StudentId
FROM (
SELECT DISTINCT StudentId
FROM MyTable --Could use your student table
WHERE CHARINDEX(studentId + ',', @input + ',') > 0) x
ORDER BY CHARINDEX(studentId + ',', @input + ',')

SELECT SubjectId
FROM MyTable t
INNER JOIN @InputTable i ON t.[Rank] = i.[Rank] AND t.[StudentId] = i.[StudentId]
GROUP BY SubjectId
HAVING COUNT(* ) = (SELECT COUNT(* ) FROM @InputTable)
AND COUNT(* ) = (SELECT COUNT(* ) FROM MyTable WHERE SubjectId = t.SubjectId)

Previous Answer: MySQL and not including requests from comments.
Example Code

Query examples in a many-to-many relationship

The first thing I would do is recommend using an ORM like Linq-To-Sql or NHibernate which will give you object representations of your data-model which make it much simpler to handle complex things like many-to-many CRUD operations.

If an ORM isn't part of your tool set then here is how this would look in SOL.


Users UserAddresses Addresses
======= ============= =========
Id Id Id
FirstName UserId City
LastName AddressId State
Zip

Our tables are joined like this:


Users.Id -> UserAddresses.UserId
Addresses.Id -> UserAddresses.AddressId
  • All records in Users based on Addresses.Id

SELECT Users.*
FROM Addresses INNER JOIN
UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
Users ON UserAddresses.UserId = Users.Id
WHERE (Addresses.Id = @AddressId)
  • All records in Addresses based on Users.Id

SELECT Addresses.*
FROM Addresses INNER JOIN
UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
Users ON UserAddresses.UserId = Users.Id
WHERE (Users.Id = @UserId)

Single SQL query on many to many relationship

You can use the GROUP_CONCAT function

select p.*, group_concat(DISTINCT c.title ORDER BY c.title DESC SEPARATOR ', ')
from Posts p
inner join PostCategories pc on p.ID = pc.ID_Post
inner join Categories c on pc.ID_Category = c.ID
group by p.id, p.title, p.content

How to select rows in a many-to-many relationship? (SQL)

If you want students with all your required courses, you can use aggregation and having:

SELECT sc.StudentId 
FROM #StudentCourses sc JOIN
@CourseList cl
ON sc.CourseID = cl.id
GROUP BY sc.StudentId
HAVING COUNT(DISTINCT sc.CourseId) = (SELECT COUNT(*) FROM @DcourseList);

If you want additional information about students, you can join in the Students table (or use a IN or a similar construct).

Note that this only needs the StudentCourses table. It has the matching ids. There is no need to join in the reference tables.



Related Topics



Leave a reply



Submit