SQLite - complex queries

Question

Hi, I am getting random row from my table using the following query:

SELECT value_id FROM table1 ORDER BY RANDOM() LIMIT 1

I have 3 tables: table1 and table2 - they are tables that have an id of a row and its value. there is as well table3 which has as its columns IDs of rows in table1 and table2.

i.e. :

table1:
1 - Canada
2 - USA
3 - UK
4 - France
5 - Africa
6 - China

table2:
1 - New York
2 - Vancouver
3 - Paris
4 - London
5 - Ottawa
6 - Los Angeles

table3: (without table3 rowid)
1 - 2
1 - 5
2 - 1
2 - 6
3 - 4
4 - 3

So from table 3 I can say that New York is a city in USA, and so on. And as well in table3 I can have undefined values. (In the example they are Africa and China so I don't want to get them in a result)

What I want to do is to get from table1 random IDs but only those that are defined in table3. In this particular case I don't want to receive 5 and 6 as a result.

How can I rewrite my query to achieve my goal?

Thank you in advance.

Edit: The problem is that when I am trying to get a row which row in table3 is not defined, application cruses. It is not NULL, as I wrote before, it just doesn't exist in table3. How can I rewrite the query to properly handle my case?

Answer 1

Not an SQLite question but a generic SQL issue. You want to test for NULL in the field values, e.g.

SELECT f1 FROM table
 WHERE f2 IS NOT NULL
ORDER BY RANDOM() LIMIT 1;

Answer 2

You haven't told us how the fields in table3 are named, but supposing for example they're named tab1_id and tab2_id with the obvious meanings, try:

SELECT table1.value_id 
FROM table1 INNER JOIN table3 ON table1.value_id=table3.tab1_id
WHERE table3.tab2_id IS NOT NULL
ORDER BY RANDOM() LIMIT 1

Answer 3

Order by Random() means that it will randomly order by different column.

Use this:

SELECT value_id FROM table3
  WHERE rowid>=random() % (SELECT max(rowid) FROM table3) 
  AND second_value IS NOT NULL
  LIMIT 1;

don't know the name of column2, so I've called it second_value

Answer 4

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 ALL columns from table1 that are in table3, then you should remove the "ORDER BY RANDOM() LIMIT 1" clause.