2

I'm creating Android countries quiz based on built-in SQLite. One of the task is to choose correct continent (from 4 options) for given country. I use the following DB structure:

continents: ID | name
countries: ID | continent (foreign key) | name | capital

App choose 4 random rows and return Cursor this way:

return db.rawQuery("SELECT country.name, continent.name FROM "+COUNTRIES_TABLE_NAME+" country LEFT JOIN "+CONTINENTS_TABLE_NAME+" continent ON country.continent = continent.id ORDER BY RANDOM() LIMIT 4", null);

What is the problem: SQLite can random countries that are from the same continent, then I have two or more equal answers. I wanted to avoid this problem adding DISTINCT:

SELECT country.name, DISTINCT continent.name...

Then I got an error (because of syntax, probably). What do I have to do then?

Marc
  • 16,170
  • 20
  • 76
  • 119
Krzysztof Antoniak
  • 451
  • 1
  • 6
  • 20

3 Answers3

3

Maybe not the most beautiful SQL, but this should do it using one SELECT to get 4 distinct contintents, and another to get a random country in each;

SELECT 
  (SELECT name FROM countries c WHERE c.continent=con.id 
   ORDER BY RANDOM() LIMIT 1) name,
  con.name
FROM (SELECT id,name FROM continents ORDER BY RANDOM() LIMIT 4) con

SQLFiddle here (SQLfiddle has a problem with calling RANDOM in SQLite WebSQL, so don't let it switch)

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Joachim - wow that is an interesting issue with WebSQL. Here's a bug report mentioning it : http://code.google.com/p/chromium/issues/detail?id=63975 and also I found a few SO questions related to it; here's one: http://stackoverflow.com/q/3500842/808921 Good thing I have got SQL.js up there too, I guess! – Jake Feasel Nov 04 '12 at 02:55
0

If you want distinct country and continent pair put distinct before country.name

See @Joachim Isaksson answer - it works!

Vrashabh Irde
  • 14,129
  • 6
  • 51
  • 103
  • Well, in this case it's not enough. Each pair in the table is unique. I don't want to get any combination where continent name is used one than more, even if country name is unique. – Krzysztof Antoniak Nov 01 '12 at 18:09
  • So if you have `Asia - India - 1 - Delhi , Asia - Srilanka - 2 -Colombo, Europe -France -3 - Paris` , you want only 1 and 3 in your output ? – Vrashabh Irde Nov 01 '12 at 18:12
  • I want whole rows, in this case only first and third or second and third. First and second is incorrect, because Asia is two times. – Krzysztof Antoniak Nov 01 '12 at 18:15
  • Why do you have left join - do you have countries without continents? Also do you have more than 4 distinct records in your data? – Vrashabh Irde Nov 01 '12 at 18:31
  • In table countries there are only integers that are IDs of continents names from the other table. Database contains all countries (there are no copies, but many countries can exist on one continent) of the world and i made it this way to use less space. Left join is needed to get name of the continent where country is placed. Edit: each country has continent, I just used left join. – Krzysztof Antoniak Nov 01 '12 at 18:35
0

Try:

"SELECT DISTINCT country.name, continent.name 
 FROM "+COUNTRIES_TABLE_NAME+" country 
   LEFT JOIN "+CONTINENTS_TABLE_NAME+" continent ON country.continent = continent.id 
 ORDER BY RANDOM() LIMIT 4"
vignesha
  • 41
  • 2