Create query drawing on a table with a compound key-SOLVED

[MSPhobe]

I’ve writen this up more fully at https://trixiesgems.com/2022/10/01/database-about-books-in-a-library/… and you can fetch a small .odb from there that has the database I’m talking about in the following.

I have a database tracking books in a library. Each copy of every book has a unique BookID. One table gives the Author, etc, associated with each BookID. A second table (“BookWhere”) has a compound primary key… The BookID used in the first table, with which a relationship is in place, and a date. The date is for when that book was seen on that shelf. (New records can only be added to “BookWhere” once a day… to prevent it from having non-unique values in the fields making up the primary key.)

“BookWhere”, also has a field to say what shelf the book is on.

BookWhere can have several entries for any particular book. This will happen when where the book is shelved changed. Instead of finding the book’s BookWhere entry, and changing the value in the “Shelf” field, I want to keep the history of the different places a book has been over the years.

HELP SOUGHT: How do I put together a query that will generate a table with columns for…

The book ID
The book title
What shelf the book was on to generate the record behind the line in the query result
When the book was seen on that shelf

If I’ve explained it in a way that gets my question across, you will see that there may be MULTIPLE entries for one book in the table produced by the query, reflecting the multiple records like them in BookWhere. They will share a BookID, but the “When” will be different for each.

Remember- you can fetch the database in question from the link above.

[RobertG]

Could be I understand totally wrong:

SELECT "Book"."Title",  "BookWhere"."BookID", "BookWhere"."Shelf" 
FROM "BookWhere", "Book" 
WHERE "BookWhere"."BookID" = "Book"."BookID"

Will show “Title”, “BookID” and “Shelf”.

SELECT "Book"."Title", 
LIST("BookWhere"."BookID") AS "BookIDs",  
LIST("BookWhere"."Shelf") AS "Shelfs" 
FROM "BookWhere", "Book" 
WHERE "BookWhere"."BookID" = "Book"."BookID" 
GROUP BY "Book"."Title"

will show all “BookIDs” and “Shelfs” for one Title in a row.

But I don’t know what you are trying to get there. If I read “BookID” I would get one title for one ID …

[MSPhobe]

Dear Robert- THANK YOU! That does indeed work. And thank you for the “bonus” information, illustrating the “list” command.

I thought I’d tried what you suggest. AND I stupidly failed to save my failed version. I did it with the GUI query builder, and tried your suggestion via “Create Query In SQL View”. Maybe that made a difference.

Ah well. As I say… thank you. Tom