Maybe you don't understand. I'll make an example TABLE POSTS:
POST_ID | POST_TITLE 1 | My first post 2 | My second post! Yay! TABLE CATEGORIES CAT_ID | CATEGORY_TITLE 1 | General 2 | Blog 3 | Sport 4 | Comic TABLE POSTS_CATEGORIES POST_ID | CATEGORY_ID 1 | 1 1 | 2 1 | 3 2 | 2 2 | 4 What does it mean? That "My first post" have 3 categories: General, Blog, Sport and "My second post! Yay!" have 2 categories: Sport and Comic So with this query (where I want to load ALL POST with their categories): SELECT posts.post_id, posts.post_title, categories.category_name FROM posts LEFT OUTER JOIN posts_categories ON posts_categories.post_id=posts.post_id LEFT OUTER JOIN categories ON posts_categories.category_id=categories.category_id; I'll get this results: 1 | My first post | General 1 | My first post | Blog 1 | My first post | Sport 2 | My second post! Yay! | Blog 2 | My second post! Yay! | Comic And I think this is the only efficent way to load all my data using only 1 query to optimize it. But: how can I handle all data in my ListFragment with my own Adapter that extend CursorAdapter? I cannot display all 5 post because as you can see we have repetitions. On Fri, Jun 10, 2011 at 2:15 AM, Zsolt Vasvari <zvasv...@gmail.com> wrote: > > Use a WHERE clause so you only get 2 rows returned. > On Jun 10, 6:17 am, Emanuele Ricci <ste...@gmail.com> wrote: > > Good evening ( for me ), I'm Emanuele a young android developer. > > I want to share with you my problem because I'm not finding a good > solution. > > > > I've 3 tables (in reality 5) > > > > posts > > categories > > posts_categories > > > > to create the many-to-many relation between posts and categories > > > > I've created a ListFragment to show all my posts with their categories. > > > > So I've created this query ( I don't write all the code [image: :)] ) > > > > SELECT posts._id, posts.post_title, categories.category_name FROM posts > LEFT > > OUTER JOIN posts_categories ON posts_categories.post_id=posts.post_id > LEFT > > OUTER JOIN categories ON > > posts_categories.category_id=categories.category_id; > > > > So if I have 2 post with 2 category each that query will return 4 > records. > > Am I wrong? > > So in my Cursor I have 4 record but what I really need is to collect > datas > > from the cursor and display only what I really need. > > > > In this case I cannot use a CursorAdapter to display my data because it > will > > insert 4 item in the ListFragment. > > > > And I don't want to load only post and than for each post load categories > > because if I have 100 posts I will do 1 query to select all posts and 100 > > query to select categories. > > > > What can I do? I need an expert advice! Which is the best way to handle > this > > situation? > > Have you ever faced this problem? > > > > Thank you very much. Emanuele Ricci. > > -- > You received this message because you are subscribed to the Google > Groups "Android Developers" group. > To post to this group, send email to android-developers@googlegroups.com > To unsubscribe from this group, send email to > android-developers+unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/android-developers?hl=en > -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
