Unexpected behaviour with sqlite

Question

I have 2 tables, 1 is {number,letter} the other {number,number}

I want to select {number,letter} where number is mentioned anywhere in {number,number}

It looks like your post is mostly code; please add some more details. So I'll waffle on for a little while after having gone to some trouble to make the issue extremely easy reproduce via the code below.

/*
    Unexpected behaviour with sqlite.

    Save this as problem.sql and do sqlite3 < problem.sql
    to demonstrate the effect.

    I have 2 tables, 1 is {number,letter} the other {number,number}

    I want to select {number,letter} where number is mentioned 
    anywhere in {number,number}

    So my query is 
    SELECT ALL num,letter
    FROM numberLetter
    WHERE num IN (
    (
    SELECT n1 FROM pairs
    UNION
    SELECT n2 FROM pairs;
    )
    );

    I've actually wrapped this up with some views in the sql below,
    results are the same whatever way you do it.

    I think I'm making a stupid mistake or have a conceptual problem?

    results:
    $ sqlite3 < problem.sql
    this is pairList
    n1
    2
    3
    4
    5
    7
    8

    this is selectedNumberLetter which I expect to have 6 rows...
    num|letter
    2|b
*/
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE pairs(
  "n1" TEXT,
  "n2" TEXT
);
INSERT INTO pairs VALUES('2','3');
INSERT INTO pairs VALUES('5','4');
INSERT INTO pairs VALUES('8','7');
CREATE TABLE numberLetter(
  "num" TEXT,
  "letter" TEXT
);
INSERT INTO numberLetter VALUES('1','a');
INSERT INTO numberLetter VALUES('2','b');
INSERT INTO numberLetter VALUES('3','c');
INSERT INTO numberLetter VALUES('4','d');
INSERT INTO numberLetter VALUES('5','e');
INSERT INTO numberLetter VALUES('6','f');
INSERT INTO numberLetter VALUES('7','g');
INSERT INTO numberLetter VALUES('8','h');
INSERT INTO numberLetter VALUES('9','i');
INSERT INTO numberLetter VALUES('10','j');

CREATE VIEW pairList AS 
SELECT n1 FROM pairs
UNION
SELECT n2 FROM pairs;

CREATE VIEW selectedNumberLetter AS 
SELECT ALL num,letter
FROM numberLetter
WHERE num IN (
(
    SELECT n1 FROM pairList
)
);
COMMIT;

SELECT 'this is pairList';
.header on
SELECT * FROM pairList;
.header off
SELECT '
this is selectedNumberLetter which I expect to have 6 rows...';
.header on
SELECT * FROM selectedNumberLetter;

Answer 1

The problem is here:

WHERE num IN ((SELECT n1 FROM pairList));

You must not enclose the SELECT statement in another set of parentheses, because if you do then SQLite will finally return only 1 row from pairList instead of all the rows.

Change to:

CREATE VIEW selectedNumberLetter AS 
SELECT num,letter
FROM numberLetter
WHERE num IN (SELECT n1 FROM pairList);

But it is easier to solve this requirement with EXISTS:

select n.* 
from numberLetter n
where exists (select 1 from pairs p where n.num in (p.n1, p.n2));

See the demo.