sql select query for one row in one table and multiple row from another table

I have tables like post,tag and post_tag .. Each post have some tags.. I want to get data like

post 1 with tagA, tagB, tagC post 2 with tagB, tagD . ..

post N with tagZ

how is the right sql statement?

pos开发者_Python百科t: id(int), title(varchar), text(varchar) tag: id(int), title(varchar) post_tag: id(int), post_id(int), tag_id(int) -> post_id foreign key on post table and tag_id foreign key on tag table.

for example i want fetch all posts with tags title.

1 "post1" "somepost bla bla" "tag1 title " "tag2 title" . ..

Try using group_concat to list all tags for a given post in a single field. See http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

you can first create a view of tags grouped by post then you can use query to get your desired result. More specific answer could be given if you actually post the details of your tables and their attributes