How to use ConcatRelated() on a many-to-many relationship

Question

As a personal education experiment, I am building a database for an imaginary animal shelter. I am quite new to SQL and databases in general.

Task: Finding all the owners of a pet in an animal shelter database.

I have three tables: Pets, Owners, and Pets2Owners. Owners can have one or many pets, and pets can have one, many, or no owners. The tables look like this:

Pets
+--------+----------+
| ID     | Name     |
+--------+----------+
|      1 | Opal     |
|      2 | Fuzzy    |
|      3 | Angel    |
|      4 | Peewee   |
|      5 | Spike    |
+--------+----------+

Owners
+----------+--------+
| ID       | Name   |
+----------+--------+
|        1 | Shy    |
|        2 | Belle  |
|        3 | Velvet |
|        4 | Brilla |
+----------+--------+

Pets2Owners
+----+--------+----------+
| ID | Pet_ID | Owner_ID |
+----+--------+----------+
|  1 |      1 |        2 |
|  2 |      2 |        1 |
|  3 |      3 |        1 |
|  4 |      5 |        3 |
|  5 |      5 |        4 |
+----+--------+----------+

So, from Pets2Owners, we see that Pet #3 (Angel) is owned by Owner #1 (Ms. Shy), Pet #5 (Spike) is owned by both Owners #3 & 4 (Velvet and Brilla), and Pet #4 (Peewee) is owned by no one.

I an inexperienced in SQL, and I'm not sure this is possible, but is there a way to make a single query that gives me all the information on pets and owners without duplicating pets, like this:

+--------+--------+
|  Pets  | Owners |
+--------+--------+
| Opal   | Belle  |
| Fuzzy  | Shy    |
| Angel  | Shy    |
| Peewee | (none) |
| Spike  | Velvet |
|        | Brilla |
+--------+--------+

Essentially, I want a way to combine information from all three tables into one. I have tried using joins, but they leave me with duplicate entries (e.g. two rows for Spike, one with the owner Velvet, the other with the owner Brilla).

Alternately, is it possible to make a query that would return all the owners of a particular pet, like so:

Spike's Owners
+--------+
| Owners |
+--------+
| Velvet |
| Brilla |
+--------+

I could use this to accomplish the same goal and have all the owners of a particular pet listed (although I'm not sure if SQL has variables that would let me make a single query for a variable PetName, then repeat it for a arbitrary name)

Answer 1

You can use Allen Browne's function ConcatRelated() to create a single row like this:

| Spike | Velvet, Brilla |

ConcatRelated() is designed for one-to-many relationships. Since you have many-to-many, you need an intermediate query that combines Pet IDs with Owner names:

SELECT Pets2Owners.Pet_ID, Owners.OwnerName
FROM Pets2Owners INNER JOIN Owners ON Pets2Owners.Owner_ID = Owners.ID;

I called this query Pet_OwnerNames.

Then ConcatRelated() is straightforward:

SELECT Pets.PetName, 
       ConcatRelated("OwnerName", "Pet_OwnerNames", "Pet_ID = " & ID) AS Owners
FROM Pets;

Result:

+---------+----------------+
| PetName |     Owners     |
+---------+----------------+
| Opal    | Belle          |
| Fuzzy   | Shy            |
| Angel   | Shy            |
| Peewee  |                |
| Spike   | Velvet, Brilla |
+---------+----------------+