Android SQLite query multiple tables

Question

Im trying to do something like this (This is MySQL format)

SELECT q.question_id, q.question, q.answer
FROM relationship r, questions q
WHERE r.cat_id =1
AND r.question_id = q.question_id
LIMIT 0 , 30

So, I got questions stored in one table, then categories in another table. I have the relationship table set up so that a question can be in multiple categories. So say a question has an id of 5, and its in 3 different categories, the relationship table would look like this

relation_id, question_id, category_id
    1            5            1
    2            5            2
    3            5            3
    4            6            1

So, say I wanna get all the questions with the cat_id of 1, I should get 2 results. That's basically what I'm trying to do.

mu is too short · Accepted Answer · 2011-07-31 19:39:02Z

3

If you want all questions with cat_id of 1, then you want this:

select q.question, q.answer
from questions q
join relationship r on q.question_id = r.question_id
where r.cat_id = 1

I've switch to the ANSI join syntax rather than implied join conditions in the WHERE clause because the explicit version helps to avoid certain types of errors; in particular, ANSI joins help avoid accidental cross products and that's what your "MySQL format" query has because you neglected to include a join condition for category. That accidental cross product is almost certainly the source of your "returns each item 3 times" problem.

answered Jul 31, 2011 at 19:39

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shaun Over a year ago

What I am looking for is the statement to get this to work in SQLite in android though.

mu is too short Over a year ago

@Shaun: You're probably looking for rawQuery in android.database.sqlite.SQLiteDatabase (or one of its friends).

Wave · Accepted Answer · 2011-07-31 19:32:39Z

1

SELECT q.question, q.answer
FROM questions q
left join category c on q.catID = c.catID
left join relationship r on q.relID = r.relID

Some thing like this will de the trick

answered Jul 31, 2011 at 19:32

Wave

5721 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Android SQLite query multiple tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related