How to know if a row doesn't exist?

Question

Hi, I have the following query:

SELECT rowid FROM table1 ORDER BY RANDOM() LIMIT 1

And as well I have another table (table3). In that table I have columns table1_id and table2_id. table1_id is a link to a row in table1 and table2_id is a link to a row in another table.

I want in my query to get only those results that are defined in table3. Only those that have table1 rowid in their table1_id column. There may not be any columns at all referring to a certain table1 rowid so in this case I don't want to receive them.

How can I achieve this goal?

Update: I tried the following query, which doesn't work:

SELECT rowid FROM table1
WHERE rowid IN (SELECT table1_id FROM table3 WHERE table1_id = table1.rowid)
ORDER BY RANDOM() LIMIT 1

Answer 1

SELECT rowid FROM table1
WHERE rowid IN ( SELECT DISTINCT table1_id FROM table3 )
ORDER BY RANDOM() LIMIT 1;

This query means "choose at random a row from table1 which has an entry in table3".

Every row in table1 equal likelihood of being selected (DISTINCT) as long as it is referenced at least once in table3.

If you are trying to get more than one result, then you should remove the "ORDER BY RANDOM() LIMIT 1" clause.

Answer 2

Assuming you want to select more than just a rowid, you need to SELECT from a JOIN between the tables you're interested in. SQLite doesn't have the full set of standard JOIN functionality, so you'll need to re-work your query so it can use a LEFT OUTER JOIN.

SELECT table1.rowid, table1.other_field
FROM table3
    LEFT OUTER JOIN table1 ON table3.table1_id = table1.rowid
ORDER BY RANDOM()
LIMIT 1;