SQLite: Example for putting a many-to-many relationship in a single column

many-to-many.sql

CREATE TABLE "users" (
	"id"	INTEGER NOT NULL UNIQUE,
	"email"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO "users" ("id", "email") VALUES
(1, "alice@example.com"),
(2, "bob@example.com"),
(3, "charlie@example.com");


CREATE TABLE "roles" (
	"id"	INTEGER NOT NULL UNIQUE,
	"name"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO "roles" ("id", "name") VALUES
(1, "Admin"),
(2, "Moderator"),
(3, "User");

CREATE TABLE "roles_users" (
	"userid" INTEGER,
	"roleid" INTEGER,
	PRIMARY KEY ("userid", "roleid"),
	FOREIGN KEY ("userid") REFERENCES "users" ("id"),
	FOREIGN KEY ("roleid") REFERENCES "roles" ("id")
);

INSERT INTO "roles_users" ("roleid", "userid") VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 1),
(3, 2),
(3, 3);

-- v1 from https://stackoverflow.com/a/56869075
SELECT
u.id AS user_id,
u.email,
IFNULL(u_roles.role_name, '') AS role_name

FROM users u LEFT JOIN (
	SELECT
		GROUP_CONCAT(DISTINCT r.name) AS role_name,
		ru.userid 
	FROM
		roles AS r
		INNER JOIN roles_users AS ru ON r.id = ru.roleid 
	GROUP BY
		ru.userid
) u_roles ON u.id = u_roles.userid;

-- v2 (assumes all users have a role)
SELECT
	u.id AS user_id,
	u.email,
	userroles.roles
FROM
	users u
	JOIN (
		SELECT
			ru.userid,
			GROUP_CONCAT(DISTINCT r.name) AS roles
		FROM
			roles AS r
			JOIN roles_users AS ru ON r.id = ru.roleid
		GROUP BY
			ru.userid
	) AS userroles ON u.id = userroles.userid;

-- v3 Thanks to @mnlttt
SELECT
  u.id,
  u.email,
  GROUP_CONCAT(r.name) AS roles
FROM users AS u
JOIN roles_users AS ru ON u.id = ru.userid
JOIN roles AS r ON ru.roleid = r.id
GROUP BY u.id, u.email