Sql: Many-To-Many Table and Query

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.

SQL many to many select

It's a straightforward inner join of the tables:

SELECT m.name, cp.id_category
FROM manufacturer as m
INNER JOIN product as p
    ON m.id_manufacturer = p.id_manufacturer
INNER JOIN category_product as cp
    ON p.id_product = cp.id_product
WHERE cp.id_category = 'some value'

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.

More than two tables with many to many relationship

You can create a table i think for storing relations. Where you can use all 3 IDs as foreign Key's.

Relation Table.

RELPKID    TeacherFKID   BookFKID  StudentFKID

1           1            2   

I hope this helps thank you

How to select multiple many to many in relation with a single table

You are close. This should work...

SELECT 
VidTbl.Name, 
ActorTbl.Name, 
SubTitelTbl.name
FROM VidTbl
INNER JOIN ActorInVid ON VidTbl.Id = ActorInVid.FKVidId 
INNER JOIN ActorTbl ON ActorTbl.Id = ActorInVid.FKActorId
INNER JOIN SubTitelInVid ON VidTbl.Id = SubTitelInVid.FKVidId
INNER JOIN SubTitelTbl ON SubTitelTbl.Id = SubTitelInVid.FKSTId

MYSQL - Query many to many relation on multiple results

x.flg_admin is not taken from the row of the table for the selected player, it's coming from an arbitrary row in the group.

You need to join with the player_group table twice so you can get this column from the correct row.

SELECT g.name, COUNT(*) as num_players, y.flg_admin 
FROM player_group x 
JOIN `group` g ON x.id_group=g.id 
JOIN player_group y ON y.id_group = x.id_group
WHERE y.id_player = 27
GROUP BY x.id_group

DEMO

BTW, you shouldn't use group as a table or column name, because it's a reserved keyword.

Multiple many-to-many relationships in SQL

Generally, you need to place the foreign key in the many end of an ERD, so in this case you might have a field called 'ownerid' in the table 'tblPROJECTS', as well as having 'ownerid' in tblTASKS. Assuming then that all tasks have a job ID and an owner, and all projects also have an owner, you can use INNER JOINs:

SELECT P.jobnumber,T.tasknumber,O1.id AS taskowner,O2.id AS jobowner
FROM tblTASKS T
INNER JOIN tblPROJECTS P ON P.jobnumber=T.jobnumber
INNER JOIN tblOWNERS O1 ON O1.id=T.ownerid
INNER JOIN tblOWNERS O2 ON O2.id=P.ownerid
WHERE O1.id=1

This will not concatenate the jobowners and task owners as you have described, but will return a row for each, which you can then concatenate whilst processing the resultset.

Then just replace the WHERE clause as necessary to get the list of tasks for a given Job number...

WHERE P.jobnumber=1000

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