You should be using INNER JOIN rather than OUTER one. Daniel
On Fri, Jun 10, 2011 at 8:24 AM, Emanuele Ricci <ste...@gmail.com> wrote: > 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 -- Daniel Drozdzewski -- 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
