In SQLite, I have the following query
SELECT x.nameIndex, y.nameIndex
FROM relation x, relation y
WHERE x.label=y.label AND x.feature=1 AND y.feature=0;
which returns all pairs of x.nameIndex,y.nameIndex with the same label where x has feature 1 and y has feature 0.
Now I have another table index2name where I store the name for each index, where I could do like:
SELECT name FROM index2name WHERE nameIndex=...;
How can I change the top query such that it looks up the name for the respective indeces and returns pairs of names instead?
Use a CTE which returns the name instead of the indexes and the group for each row in relation (by a join to index2name) and do a self join on that:
WITH cte AS (
SELECT i.name, r.label, r.feature
FROM relation r INNER JOIN index2name i
ON i.nameIndex = r.nameIndex
)
SELECT c1.name, c2.name
FROM cte c1 INNER JOIN cte c2
ON c2.label = c1.label
WHERE c1.feature=1 AND c2.feature=0;
Or without the CTE:
SELECT i1.name, i2.name
FROM relation r1 INNER JOIN relation r2
ON r2.label = r1.label
INNER JOIN index2name i1 ON i1.nameIndex = r1.nameIndex
INNER JOIN index2name i2 ON i2.nameIndex = r2.nameIndex
WHERE r1.feature=1 AND r2.feature=0;