Ndtyjky

I have two joints but I do not want the same row again.

that works with select distinct.

but for performance reasons, I wanted to check that soon in the where clause with:
and x.ROWID != w.ROWID

but I get the following error:

[SQLITE_ERROR] SQL error or missing database (no such column: w.ROWID)

Is that possible?

here the complete select:

select distinct substr(w.wordindexed, 1, 55), substr(w.word, 1, 15), w.wordreplacement

from Words w

       JOIN (SELECT x.ActionListId, x.wordindexed, x.word, x.wordreplacement

             from Words x

             where x.word like "%Hallo %"

               and x.ROWID != w.ROWID

             limit 1)

       JOIN (SELECT p.ActionListId, p.wordindexed, p.word, p.wordreplacement

             from Words p

             where p.word like "%Tool%"

               and p.ROWID != x.ROWID

             limit 1

            )

limit 5;

Example result:

3   HALLO VON _GLOBALACTIONLISTS_GLOBAL.AHK    Hallo von _globalActionLists_global.ahk    ""

6   HALLO WELT  Hallo Welt  ""

9   HALLO VON _GLOBALACTIONLISTS_GLOBAL.AHK    Hallo von _globalActionLists_global.ahk    ""

Example insert:

INSERT INTO Words (ActionListID, wordindexed, word, lineNr, count, worddescription, wordreplacement) VALUES (3, 'HALLO VON _GLOBALACTIONLISTS_GLOBAL.AHK', 'Hallo von _globalActionLists_global.ahk', 5, null, null, '');

INSERT INTO Words (ActionListID, wordindexed, word, lineNr, count, worddescription, wordreplacement) VALUES (3, 'STOP CHANGEING LIST|RR||AHK|G_CONFIG["LIST"]["CHANGE"]["STOPREXEXTITLE"]:="."', 'stop changeing list|rr||ahk|g_config["list"]["change"]["stopRexExTitle"]:="."', 7, null, null, '');

INSERT INTO Words (ActionListID, wordindexed, word, lineNr, count, worddescription, wordreplacement) VALUES (3, ' DONT CHANGEING LIST|RR||AHK|

', ' dont changeing list|rr||ahk|

', 9, null, null, '');

INSERT INTO Words (ActionListID, wordindexed, word, lineNr, count, worddescription, wordreplacement) VALUES (6, 'HALLO WELT ', 'Hallo Welt ', 9, null, null, '');

INSERT INTO Words (ActionListID, wordindexed, word, lineNr, count, worddescription, wordreplacement) VALUES (9, 'HALLO VON _GLOBALACTIONLISTS_GLOBAL.AHK', 'Hallo von _globalActionLists_global.ahk', 5, null, null, '');

the tabke:

create table Words

(

    ActionListID INTEGER not null,

    wordindexed TEXT not null,

    word TEXT not null,

    lineNr INTEGER,

    count INTEGER,

    worddescription TEXT,

    wordreplacement TEXT not null,

    primary key (ActionListID, word, wordreplacement)

)

;



create index WordIndex

    on Words (ActionListID, wordindexed)

;