Sqlite nested SELECT with JOIN and RANDOM()

I have a main table (split into three tables bound together by a relations table, since there are many-to-many relations) and another table which stores information about the posts in the main table.

I'm executing a nestedSELECTwhich doesn't seem to work. However, if I separately execute theSELECTSand manually "transfer" the result, there's no problem. (I'm using DB Browser for Sqlite, and I just uncomment the two "--" and run each statement.)

CREATE TABLE IF NOT EXISTS "mainTableOne" ( 'fieldOne' TEXT NOT NULL CHECK ( LENGTH ( fieldOne ) > 0 ) UNIQUE PRIMARY KEY );
CREATE TABLE IF NOT EXISTS "mainTableTwo" ( 'fieldTwo' TEXT NOT NULL CHECK ( LENGTH ( fieldTwo ) > 0 ) UNIQUE PRIMARY KEY );
CREATE TABLE IF NOT EXISTS "mainTableThree" ( 'fieldThree' TEXT NOT NULL CHECK ( LENGTH ( fieldThree ) > 0 ) UNIQUE PRIMARY KEY );
CREATE TABLE IF NOT EXISTS "mainRelations" ( 'fieldOne' INTEGER NOT NULL CHECK ( fieldOne > 0 ) , 'fieldTwo' INTEGER NOT NULL CHECK ( fieldTwo > 0 ) , 'fieldThree' INTEGER NOT NULL CHECK ( fieldThree > 0 ) , PRIMARY KEY ( fieldOne , fieldTwo , fieldThree ), FOREIGN KEY ( 'fieldOne' ) REFERENCES 'mainTableOne' ( 'rowid' ) , FOREIGN KEY ( 'fieldTwo' ) REFERENCES 'mainTableTwo' ( 'rowid' ) , FOREIGN KEY ( 'fieldThree' ) REFERENCES 'mainTableThree' ( 'rowid' ));

CREATE TABLE IF NOT EXISTS "extraTable" ( 'infoField' TEXT NOT NULL CHECK ( LENGTH ( infoField ) > 0 ) UNIQUE PRIMARY KEY , 'grupp' TEXT NOT NULL , 'antal' INTEGER NOT NULL CHECK ( LENGTH ( antal >= 0 )) DEFAULT 0 );
CREATE TABLE IF NOT EXISTS "extraRelations" ( 'fieldOne' INTEGER NOT NULL CHECK ( fieldOne > 0 ) , 'fieldExtra' INTEGER NOT NULL CHECK ( fieldExtra > 0 ) , PRIMARY KEY ( fieldOne , fieldExtra ) , FOREIGN KEY ( 'fieldOne' ) REFERENCES 'mainTableOne' ( 'rowid' ) , FOREIGN KEY ( 'fieldExtra' ) REFERENCES 'extraTable' ( 'rowid' ));

SELECT /**/DISTINCT/**/ mainTableOne.fieldOne , mainTableTwo.fieldTwo , mainTableThree.fieldThree , mainTableOne.rowid
    FROM    mainTableOne JOIN
                mainRelations JOIN
                mainTableTwo JOIN
                mainTableThree
    ON      mainRelations.fieldOne = mainTableOne.rowid AND
                mainRelations.fieldTwo = mainTableTwo.rowid AND
                mainRelations.fieldThree = mainTableThree.rowid
    WHERE mainTableOne.rowid IN ( --3 ) ORDER BY mainTableOne.fieldOne , mainTableTwo.fieldTwo , mainTableThree.fieldThree;
        SELECT mainTableOne.rowid
            FROM    extraRelations JOIN
                        mainTableOne JOIN
                        extraTable
            ON      extraRelations.fieldOne = mainTableOne.rowid AND
                        extraRelations.fieldExtra = extraTable.rowid
            WHERE extraRelations.fieldExtra = 1
            ORDER BY RANDOM() LIMIT 3 --;
            )
        ORDER BY mainTableOne.fieldOne , mainTableTwo.fieldTwo , mainTableThree.fieldThree;

The problem I get is that sometimes there are empty extra rowsSELECTED(0 and up). I tried with and withoutDISTINCTbut neither helped. It also works without theRANDOM()but that defeats the purpose.

Can anyone spot what the problem is? Do I need to "UNJOIN" the nestedSELECTor use a different kind ofJOIN? (I am not an expert, but I think it should be theJOINI've used.)