SQLite alternative to transposition / sub-select returns N columns

I have a table of relations between primary ids that I'm trying to use to get a list of their accompanying attributes. The minimal example below will throw Error: sub-select returns 3 columns - expected 1. I've read a few solutions here that people use when reaching for transpositions, but they seem to rely on either looking for data in just one column with a small set of choices, or a similar alternative using cases. I don't believe that to be appropriate for my actual dataset given the dimensions and lack of grouping among attributes, but there's perhaps a simple alternative query I'm not seeing. Or perhaps a better way to structure the data to accommodate such a query.

CREATE TABLE Keys(primary_id, id1, id2);
CREATE TABLE Attr(id, attr1, attr2);

INSERT INTO Keys VALUES(1, 2, 3);
INSERT INTO Keys VALUES(2, 4, 5);
INSERT INTO Keys VALUES(3, 6, 7);
INSERT INTO Attr VALUES(1, "a", "b");
INSERT INTO Attr VALUES(2, "c", "d");
INSERT INTO Attr VALUES(3, "e", "f");
INSERT INTO Attr VALUES(4, "g", "h");
INSERT INTO Attr VALUES(5, "i", "j");
INSERT INTO Attr VALUES(6, "k", "l");
INSERT INTO Attr VALUES(7, "m", "n");

SELECT *
FROM Attr
WHERE Attr.id IN (SELECT * from Keys where Keys.primary_id=2)

Desired output:

| 2 | "c" | "d" |
| 4 | "g" | "h" |
| 5 | "i" | "j" |

I am able to change the Keys table if it made sense to group all the non-primary keys in some way I could parse instead. I dropped Keys and tried something like:

CREATE TABLE Keys(primary_id, ids);
INSERT INTO Keys VALUES(1, json_array('[2, 3]'));
INSERT INTO keys VALUES(2, json_array('[4, 5]'));
INSERT INTO Keys VALUES(3, json_array('[6, 7]'));

This query SELECT json_extract(ids, '$[#-1]') FROM Keys WHERE Keys.primary_id=2; returns [4,5] But,

SELECT *
FROM Attr
WHERE Attr.id IN (SELECT json_extract(ids, '$[#-1]') FROM Keys WHERE Keys.primary_id=2)

returns null. That's probably not even a valid approach if one can't select from within arrays anyway. Thanks for your collective wisdom!

EDIT: The solution for completeness:

SELECT *
FROM Attr
WHERE Attr.id IN (SELECT primary_id FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id1 FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id2 FROM Keys WHERE Keys.primary_id=2);

CodePudding user response：

In your subquery you would need to select each id column individually and union the results together i.e. your subquery will have 3 select statements unioned