Non-Linear Database Retrieval

Question

I'm building an article system, and each article will have one more Tags associated with it (similar to the Tags on this site).

The tables are set up something like this:

Article_Table
  Article_ID | Title | Author_ID |  Content | Date_Posted | IP ... 

Tag_Table
  Tag_ID | Name ...

Tag_Intersect_Table
  Tag_ID | Article_ID 

Is it possible query an article and all of its associated tags in one database call? If so, how is this done?

Answer 1

You're looking for what is called a JOIN in SQL:

SELECT
    Article_ID, Title, TT.Name as 'Tag_Name'
  FROM
    Article_Table AT
    INNER JOIN Tag_Intersect_Table TI
      ON AT.article_id = TI.article_id
    INNER JOIN Tag_Table TT
      ON TI.tag_id = TT.tag_id
  WHERE
    article_id = @my_article_id

That joins the two entity tables to the same intersection table with a natural join syntax.

Note that in this result set, you'll have one row for each combination of a tag and an article, so the article_id and title will be repeated over and over for each tag. If you're only querying against one article and want to get just the names of all the tags, you can restrict the SELECT list to just TT.name.