combine master and detail information in query result

Question

Using openoffice.org Base 3.1.1

Given the database below with three tables, I would like to create a query with the following output. How can this be achieved with queries or views?

output

book.title, tags
title 1, tagdescription1 tagdescription2 tagdescription3
title 2, tagdescription2

database

BOOK
id - primary key,title
1, title 1
2, title 2

TAG
id - primary key,name
tag1, tagdescription1
tag2, tagdescription2
tag3, tagdescription3

BOOK_TAG
book_id,tag_id
1,tag1
1,tag2
1,tag3
2,tag2

Answer 1

My first thought is that you can't really get a query to format like that as you have a dynamic amount of columns. I would join the tables and then have it duplicate the title for each tag using a join.

SELECT book.title, tag.name FROM book LEFT JOIN book_tag ON book_tag.book_id = book.id LEFT JOIN tag ON tag.id = book_tag.tag_id

Which should, give you a resultset like,

book.title     tag.name
title 1        tagname1
title 1        tagname2
title 1        tagname3
title 2        tagname2

I'm sure someone has a better way though :)