CODEWITHSUNDEEP
mysqlsql
Jack
Jack

Reputation: 1999

Get only available tags from a many to many relation with MySQL query

I have three tables with a many to many relationships; media, relations, and tags

      Media Table                      Relations Table                   Tags Table

| media-id | media-name |       | rel-id | media-id | tag-id |       | tag-id | tag-name|
-------------------------       ------------------------------       --------------------
| 1        | "Media 1"  |       | 1      | 1        | 1      |       | 1      | "tag 1" |
| 2        | "Media 2"  |       | 2      | 2        | 2      |       | 2      | "tag 2" |
                                | 3      | 1        | 3      |       | 3      | "tag 3" |
                                | 4      | 2        | 3      |       | 4      | "tag 4" |
                                | 5      | 1        | 4      |

I need a MySQL query that when a tag or tags are given, it will return all other possible tags combinations. Here are several examples.

Example 1

Given: tag 1

Returned: tag 1, tag 3 and tag 4

Because: tag 1 is tagging Media 1 so it should return all of its tags (tag 1, tag 3, and tag 4)


Example 2

Given: tag 3

Returned: tag 1, tag 2, tag 3, and tag 4 are returned

Because: tag 3 is tagging both Media 1 and Media 2 so all of their tags are returned (tag 1, tag 2, tag 3, tag 4)


Example 3

Given: tag 2 and tag 3

Returned: tag 2 and tag 3

Because: tag 3 tags both Media 1 and Media 2, but only Media 2 has tag 2 so just it's tags are returned and Media 1's tags are excluded

I've searched for something like this for a while, but I haven't found anything related with arcuate answers. The closest I was able to get to this was this query here:

SELECT `tags`.`tag-name`
FROM `tags`
WHERE `tags`.`tag-id` IN (
    SELECT `relations`.`tag-id`
    FROM `relations`
    WHERE `relations`.`media-id` IN (
        SELECT `relations`.`media-id`
        FROM `relations`
        WHERE `relations`.`tag-id` IN (
            SELECT `tags`.`id`
            FROM `tags`
            WHERE `tags`.`tag-name` IN ('tag 1')
        )
    )
)

This works great, maybe not the most effective, but I can understand it.

The only issue with this is if I were to run it on Example 3, it would return all the tags, instead of excluding tags from Media 1.

Is there a way that I could change my MySQL query to do this?

Upvotes: 0

Views: 152

Answers (2)

forpas
forpas

Reputation: 164194

The general solution to your problem involves multiple joins between the tables and aggregation, like this:

select t2.tag_name
from tags t
inner join relations r on r.tag_id = t.tag_id 
inner join media m on m.media_id = r.media_id 
inner join relations r2 on r2.media_id = m.media_id
inner join tags t2 on t2.tag_id = r2.tag_id
where t.tag_name in ('tag 2', 'tag 3')
group by t2.tag_name
having count(distinct t.tag_id) = 2

This:

('tag 2', 'tag 3')

is the list of the tags that you want the query to fetch the results.
And the number on the right side of the condition in the HAVING clause:

count(distinct t.tag_id) = 2

is the number of the items in that list.
See the demo.
In the case of only 1 queried tag the code can be simplified to:

select t2.tag_name
from tags t
inner join relations r on r.tag_id = t.tag_id 
inner join media m on m.media_id = r.media_id 
inner join relations r2 on r2.media_id = m.media_id
inner join tags t2 on t2.tag_id = r2.tag_id
where t.tag_name = 'tag 1'
group by t2.tag_name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

If you want the tags that are not used for "media_id = 1", then not exists comes to mind:

select t.*
from tags t
where not exists (select 1
                  from relations r join
                       media m
                       on r.media_id = m.media_id
                  where m.media_name = 'Media 1' and
                        r.tag_id = t.tag_id
                 );

Upvotes: 0

Related Questions