Skip to content
Advertisement

SQL How do I query a many-to-many relationship

How do I select posts that contain a specific tag if there is a many-to-many relationship between posts and tags?

The problem I am having is that because of the where tag.name = 'xxx', only that tag is selected. I want to select all posts that have the tag specified, together with all of their tags, e.g.,

Currently what I get is:

Advertisement

Answer

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.

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:

Another way to do this is:

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:

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement