Знатоки Sql

Выбросив лишние подробности имеется следующая база данный на Access'e:

1. Таблица Authors:
поля ID, Name (ID-ключевое)

2. Таблица Books:
поля ID, Caption (ID-ключевое)

и, наконец, промежуточная таблица, обеспечивающая связь "многие-ко-многим"

3. Таблица BooksAuthors:
поля Book_ID, Author_ID

в последней таблице первичным ключом является уникальная комбинация двух полей.

Теперь, допустим, в базу занесены следующие данные:

Authors:

1 Ильф И.
2 Петров Е.
3 Момзен Т.
...

Books:

1 12 стульев
2 Золотой телёнок
3 История Рима
...

BooksAuthors:
1 1
1 2
2 1
2 2
3 3

А вот теперь вопрос: как создать запрос, выдающий:

12 Стульев Ильф И., Петров Е.
Золотой телёнок Ильф И., Петров Е.
История Рима Момзен Т.

?

Создать запрос, выдающий

12 Стульев Ильф И.
12 Стульев Петров Е.
Золотой телёнок Ильф И.
Золотой телёнок Петров Е.
История Рима Момзен Т.

легко.

Если бы, к примеру, число авторов было бы ограничено, например, таблица Books имела бы вид: ID, Caption, Author1, Author2, то вопрос решался бы просто:

SELECT Caption, [Author1] & ", " & [Author2] FROM Books

Здесь подразумевается непосредственное хранение фамилий в таблице, что не есть гуд, но суть понятна: операция объединения строк склеивает строки из разных полей. Так как можно добиться подобного эффекта с использованием промежуточных таблиц для неограниченного числа полей. Желательно с использованием SQL без Visual Basic'a?

 

В общем, проблему решил следующим образом:

1. Создал запрос вроде этого:

SELECT Books.Caption AS Caption, Authors.Name AS Name FROM Authors, Books, BooksAuthors INNER JOIN (Books INNER JOIN BooksAuthors ON Author.ID = BooksAuthors.Author_ID) WHERE BooksAuthors.Book_ID = Books.ID ORDER BY Books.Caption

и обозвал его "BooksByAuthors". В нём названия книг продублированы по количеству авторов на каждую.

2. После этого написал на VB макрос:

Private Sub btnCreateQuery_Click()

Dim strQueryName As String
Dim strTableName As String
strQueryName = "BooksByAuthors" ' Запрос с продублированными названиями книг
strTableName = "BooksByAuthors_Autogenerated"

Dim db As DAO.Database
Dim qd As QueryDef
Dim qdLoop As QueryDef
Dim td As TableDef
Dim tdLoop As TableDef
Dim src_rs As Recordset
Dim dst_rs As Recordset

Set db = CurrentDb()

For Each tdLoop In db.TableDefs
If tdLoop.Name = strTableName Then
db.TableDefs.Delete strTableName
End If
Next tdLoop

Set qd = db.QueryDefs(strQueryName)

Set td = db.CreateTableDef(strTableName)
With td
.Fields.Append .CreateField("Caption", dbText, 255)
.Fields.Append .CreateField("Authors", dbMemo)
End With
db.TableDefs.Append td

Set src_rs = qd.OpenRecordset
Set dst_rs = td.OpenRecordset

Dim i As Integer
Dim CurrSectionID As Integer
Dim strCurrSection As String
Dim CurrReportID As Integer
Dim strCurrCaption As String
Dim strAuthors As String

src_rs.MoveFirst

strCurrCaption = src_rs("Caption")
strAuthors = src_rs("Name")

For i = 1 To src_rs.RecordCount - 1
src_rs.MoveNext

If src_rs("Caption") <> strCurrCaption Then ' Проверка уникальности имени книги
dst_rs.AddNew
dst_rs("Caption") = strCurrCaption
dst_rs("Authors") = strAuthors
dst_rs.Update

strCurrCaption = src_rs("Caption")
strAuthors = src_rs("Name")
Else
strAuthors = strAuthors & ", " & src_rs("Name") ' Объединение имён авторов
End If
Next i

End Sub