CODEWITHSUNDEEP
androidsql
Jack Commonw
Jack Commonw

Reputation: 395

Compare two tables in one query

I am building a small game which gets a best level and the number of levels which you completed, that is more than the half of the questions correct. I have a query which checks for this, but the problems i don't know how to integrate the questions table. If a user didn't try to answer a question, no line will be written in to the answers table. So it actually now compares to the number of lines in the answers table for each level. Any ideas on how to integrate this? The (count(*)/2) actually 

Cursor c = myDataBase
                .rawQuery(
                        "select level, (select count(*) from ( select level from answers group by level having sum (answercorrect) >= (count(*)/2) ) answers ) completedlevels, "
                                + "from answers "
                                + "group by level "
                                + "order by sum (score) desc ", null);

I tried this but it didn't work:

Cursor c = myDataBase
                .rawQuery(
                        "select level, (select count(*) from ( select level from questions group by level having sum (answercorrect) >= ((select count(*)/2 from questions group by level) ) answers ) completedlevels, "
                                + "from answers "
                                + "group by level "
                                + "order by sum (score) desc ", null);

EDIT The table structure is:

questions table
id    level   question
1      1      question1level1
2      1      question2level1
...  
30     1      question30level1
31     2      question1level2
32     2      question2level2
...  
70     2      question40level2
71
...

//note: each level can have a different numer of questions

answers table

id   question_id   player   answercorrect   score   attempts  
1     5             1        1              1000     1
2     10            1        1              900      1
3     7             2        0              700      3
4     10            2        0              500      3
5     13            2        1              100      1
6     8             1        1              800      2

...

Upvotes: 2

Views: 841

Answers (2)

Talha
Talha

Reputation: 12717

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. you can refer this url , sql_join for more detail

For example

select level from question q
inner join answer a on (a.questionID = q.questionID)

it brings level coloumn data from question table where inserted to answer table with same questionID.

you can make relation with JOIN keyword.

Also you can refer to this a-visual-explanation-of-sql-joins

I think your table structure is wrong, it is not relational. I suggest you to use below structure.

enter image description here

With below query you can find how many user did reply questions corerrectly, after than you can customize the query what ever you want.

SELECT     *
FROM       tbl_question 
INNER JOIN tbl_userAnswers ON tbl_question.id = tbl_userAnswers.userChoiceID
INNER JOIN tbl_correctAnswers ON tbl_question.id = tbl_correctAnswers.QuestionId and tbl_userAnswers.userChoiceID = tbl_correctAnswers.correctChoiceId

enter image description here

Upvotes: 2

Waleed
Waleed

Reputation: 177

What you need is called SQL Join. Simple SQL Join examples can be found here.

Let's assume you have the following data in your tables,

Employee Table:
LastName    DepartmentID
Rafferty      31
Jones         33
Steinberg     33
Robinson      34
Smith         34
John          NULL

.

Department table:
DepartmentID    DepartmentName
31              Sales
33              Engineering
34              Clerical
35              Marketing

If you use the following query...

SELECT
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

The statement in the WHERE clause is called the join condition and this is an Equi-Join. It would get you this, You'd get,

LastName   DepartmentID    DepartmentName
Rafferty      31           Sales
Jones         33           Engineering
Steinberg     33           Engineering
Robinson      34           Clerical
Smith         34           Clerical

Upvotes: 2

Related Questions