SQL Many to Many Select

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'

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 relationships select where multiple criteria

If you do all the joins as normal, then aggregate the rows to one group by article, then you can assert that they must have at least two different tags.

(Having already filtered to great and/or awesome, that means they have both.)

SELECT
    a.id_article,
    a.title
FROM
    articles a
INNER JOIN
    relations r
        ON r.id_article = a.id_article
INNER JOIN
    tags t
        ON t.id_tag = r.id_tag
WHERE
    t.title IN ('great', 'awesome')
GROUP BY
    a.id_article,
    a.title
HAVING
    COUNT(DISTINCT t.id_tag) = 2

(The DISTINCT is to avoid the possibility of one article having 'great' twice, for example.)

To do OR, you just remove the HAVING clause.

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.

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

SQL Select to display one to one, one to many from many to many relation

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by field1) as cnt1,
             count(*) over (partition by field2) as cnt2
      from t
     ) t
where cnt1 = 1 and cnt2 = 1;

You can also use not exists:

select t.*
from t
where not exists (select 1 
                  from t t2
                  where t2.field1 = t.field1 and t2.field2 <> t.field2
                 ) and      
      not exists (select 1 
                  from t t2
                  where t2.field2 = t.field2 and t2.field1 <> t.field1
                 ) ;
      

Complex query in a many to many relationship

Let me assume that you are passing the values in as an array. You can use:

select g.name
from groups g join
     user_groups ug
     on ug.group_id = g.id join
     users u
     on ug.user_id = u.id
where u.name = any (:input_list)
group by g.name
having count(*) = cardinality(:input_list);

Here is a db<>fiddle, with the data fixed to match the results you have in the question.

---

Related Topics