CODEWITHSUNDEEP
c#.netsql-servervb.net
monocular
monocular

Reputation: 323

SQL select tag for blog post

I'm creating a blog and want to show tags for all my post. Each post can have many tag. So i stored tag information in a table called tblTag , and my blog post stored in tblPost.

Currently, to show all post and all tag per post. I'm using the below method:

Select * from tblPost // this to select all post

And do a while loop per blog post:

Select * from tblTag where BlogPostID= ... // this to select tag per post

This work. But the performance is very bad. I don't want the server to run a lot of query. Anyway to do is better ?

Upvotes: 1

Views: 222

Answers (2)

user3787846
user3787846

Reputation:

You can use a SQL query to get the post information (title, date, etc), and also all tag rows associated with this post. 

SELECT p.*, t.* FROM tblPost p
left join tblTag t on t.BlogPostID = p.id

Once you've got the above, you can cycle through the rows in your dataset.

Upvotes: 0

Ruslan
Ruslan

Reputation: 2799

Yes, you can use a CROSS APPLY.

SELECT p.*, LEFT(ISNULL(t.n,''), LEN(t.n) - 1)
FROM tblPost p
    CROSS APPLY (SELECT it.TagName + ',' FROM tblTag it WHERE it.BlogPostId = p.BlogPostId FOR XML PATH('')) t(n)
WHERE p.BlogPostID = ...

This will give you all of the tags, separated by commas, in a separate column.

Upvotes: 1

Related Questions