-3

Lets take stackoverflow as an example:

Post

Postid          Title          Mess
--------------------------------------------------
1               Title1         This is a question
2               Title2         This is a question1
3               Title3         This is a question2
4               Title4         This is a question3
5               Title5         This is a question4
6               Title6         This is a question5

Tags

TagId     PostId     Name
-----------------------------
1         1              Tag1
2         1              Tag2
3         1              Tag3
4         1              Tag4
5         2              Tag5
6         3              Tag6
7         4              Tag7
8         5              Tag8
9         6              Tag9
10        3              Tag10

In this design how would I get all the questions with its associated tags in one query. is this even possible?

*Edit*

select t.*, p.* from Tags t
join post p on t.postid=p.postid

Something like this would give me all the tags for each question. But I don't think this is efficient. What do you think.

Luke101
  • 63,072
  • 85
  • 231
  • 359
  • you mean joins? check out http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – JKirchartz Apr 02 '12 at 16:54
  • 1
    Please have a look at http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – Chetter Hummin Apr 02 '12 at 16:55
  • If you know all tags upfront and would rather not deal with multiple results per post, you could use [pivot](http://msdn.microsoft.com/en-us/library/ms177410.aspx). – Sergey Kalinichenko Apr 02 '12 at 16:55
  • 2
    Can you show what you want as output from your one query? "get all the questions with its associated tags" is rather vague and ambiguous. – Aaron Bertrand Apr 02 '12 at 16:55
  • @Aaron - I am not sure what kind of output to expect on something like this. But, I got an idea maybe I can query the tags table then join the post table and get the id, title and message. If I do this then there will be duplicate data in the results for the post table. is this effiecient? – Luke101 Apr 02 '12 at 17:02
  • Well, can you tell us what you would ideally like to see? – Aaron Bertrand Apr 02 '12 at 17:06
  • 1
    What do you think would be more efficient? This is becoming less of a question as you add more information. – Aaron Bertrand Apr 02 '12 at 17:12

1 Answers1

1

I think that your example is very weak. You shouldn't have the tag name in the table you use to join posts and tags.

You should have the following tables:

  • Posts: PostId, Title, Mess
  • Tags: TagId, Name
  • Posts_Tags: PostId, TagId

If you don't do this then you'll have many equal tag names when 2 posts have the same tag, do you see?

Now, in order to query these tables and get all questions with the associated tags you should do the following:

select * from posts p
join posts_tags pt on p.postId = pt.postId
join tags on pt.tagId = t.tagId

You can say it is less efficient but you're not considering data duplication in your example, which may cause serious errors.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123