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)

Oh, sorry! MS Access (I know, probably not the most popular thing on SO) — Somatic, Jun 27 '17 at 01:03
You can't do a query that returns an uneven number of rows (e.g., what you've indicated you want for Spike). Do that in your front end reporting or view. — Ken White, Jun 27 '17 at 01:17
@KenWhite That was kinda what I was expecting. Should I close/delete the question? — Somatic, Jun 27 '17 at 01:20
You can use [`ConcatRelated()`](http://allenbrowne.com/func-concat.html) to create a single row like this: `| Spike | Velvet, Brilla |` -- see https://stackoverflow.com/a/13280353/3820271 — Andre, Jun 27 '17 at 06:10
@Andre think you could put that comment as an answer? That's actually pretty close to my goal! — Somatic, Jun 28 '17 at 18:36
Done, I actually had to figure out myself how to do it in this situation. This is a pretty nice example for using ConcatRelated() with a many-to-many relationship, so I edited the question title accordingly, I hope you don't mind. — Andre, Jun 28 '17 at 22:09

score 0 · Answer 1 · answered Jun 27 '17 at 04:30

SELECT Pets.Name AS Pets, Owners.Name AS Owner
FROM Pets2Owner INNER JOIN Pets ON Pets2Owner.[Pet Id] = Pets.[id]
INNER JOIN Owners ON Pets2Owner.[Owner Id] = Owners.[id]

after you get a good grip of the JOINS, try to look at parameters for Access :)

score 0 · Accepted Answer · answered Jun 28 '17 at 21:56

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 |
+---------+----------------+

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

2 Answers2