In the sqlite query, how to get a specific row and column that have the required value


I am trying to build a quiz app and the entries in the database look like this

id | term | synonym 1 | synonym 1 answered | synonym 2 | synonym 2 answered | ....
1  | read | recite    |       Yes          | study     |     No             |
2  | work | effort    |       No           | labour    |     Yes            |

The idea is to present one synonym at a time. Once the next synonym is chosen, the previous synonym is marked as answered using the column next to the synonym with "Yes"

For the logic to select a word I am using a Collection.shuffle() function to get a random row and random column, query database to see if its answered column is "No". If "Yes" I am repeating the shuffle till I get a "No".

For knowing if atleast one entry in the entire table has a "No" in any of the 'answered' columns, I am using an OR clause against the answered columns (to make sure that all synonyms are not already answered)

So my app is doing lot of iterations to get the desired word which is definitely very bad way.

I am unable to figure a way to let sqlite query return me a random row and column that has the word "No". If I can get the column name of the result that had "No", I can strip the word 'answered' and get the related synonym column in a row, and present it with out much of java code.

Can any one kindly enlighten me on this and give a solution? I would require the column name and the rowid of the resulting match of a word "No" in the entire table. And it must be at RANDOM

Edit: The scenario given here is for simplicity. The actual app deals with Sanskrit grammar and requires the kind of implementation I am planning. I require to get the 'name' of the column and 'id' of the row that got a 'No'. In the implementation I won't be using 'Yes' and 'No'. The number of columns would be fixed for all the terms. For simplicity I gave this example.

I would recommend the following:

First normalize your table structure. There will be two tables: Terms and Synonyms

Terms table will have Id and TermName

In the Synonyms table there will be SynonymId, Name, Answered TermId So, you can track synonyms through TermId here.

This way, you can easily query to see what synonyms are still not answered for a specific term like:

SELECT * FROM Synomyms WHERE TermId = 1 AND Answered = "No"

Hope this helps