1

I have a table with a m:n (many to many) relationship. Basically, if I select an article_id, I want to return all the other articles that have the same tag_id as the selected article_id.

| article_id | tag_id |
|------------|--------|
| 1          | 2      |
| 1          | 11     |
| 2          | 5      |
| 4          | 6      |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 14         | 15     |
| 14         | 17     |
| 18         | 1      |
| 20         | 8      |
| 41         | 13     |
| 49         | 9      |
| 50         | 12     |
| 60         | 18     |
| 75         | 9      |
| 75         | 10     |
| 75         | 11     |

So, if i select article_id 75 i need to output all of the articles that contains tag_id 9, 10 and 11

| article_id | tag_id |
|------------|--------|
| 1          | 11     |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 49         | 9      |

I've created the following query and it looks like it works, but i want to make sure. Is there a better way?

SELECT aj2.article_id, aj2.tag_id
FROM article_tag_join aj
JOIN article_tag_join aj2 ON aj2.tag_id = aj.tag_id
WHERE aj.article_id = 75 AND aj2.article_id <> 75
1

You could change the query slightly so that the exclusion is done within the join:

SELECT DISTINCT aj2.article_id, aj2.tag_id
  FROM article_tag_join AS aj
  JOIN article_tag_join AS aj2
    ON aj.tag_id = aj2.tag_id
   AND aj.article_id <> aj2.article_id
 WHERE aj.article_id = 75;

This might offer better performance, but it is always worth comparing it against your existing query. If I have two queries that result in the same execution plan, I pick the one that is easiest to maintain.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.