Mando Madalin Mando Madalin - 5 months ago 28
MySQL Question

Search by tag id using many to many relationship mysql

Ok, so this is my problem:
I have tags system that can be attributed to users and companies. I want to extract the companies that match with user tags.

Schema is this:

Table Tags:

| id | tag_name |
| 1 | tag 1 |
| 2 | tag 2 |
| 3 | tag 3 |


Table Users:

| id | user_name |
| 1 | user1 |
| 2 | user2 |


Table Users Tags:

| id | id_user | id_tag |
| 1 | 1 | 1 |
| 2 | 1 | 3 |


Table Companies:

| id | company_name |
| 1 | company 1 |
| 2 | company 2 |


Table Companies Tags:

| id | id_user | id_company | id_tag |
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 3 |


both tags: tag1 and tag3 are present on user1 and company 1. How do i extract companies that match with user tags?

Thanks

Answer Source

Basically start at one end and keep joining: User--UserTag--CompanyTag--Company:

SELECT u.id, c.id
FROM       Users u
INNER JOIN UsersTags ut        ON u.id = ut.id_user
INNER JOIN CompaniesTags ct    ON ct.id_tag = ut.id_tag
INNER JOIN Companies c         ON ct.id_company = c.id
GROUP BY u.id, c.id

...gives pairs of (user, company) that share at least one tag.

Inner join removes rows that don't have data on both sides of the join.
Group by remove duplicates arising from multiple tags linking the same User-Company.