This is what I get: http://blobs.ge.tt/8tYJKz4/concat.png?sig=-TB4nxfSNqs5AchU5rFsughxje1g4iFms8w
Only a row :/ Am I wrong? Anyway I think that this solution doesn't fit the real scenario. I semplified a bit my query. In the real usage I want to select: SELECT posts.post_id, posts.post_title, categories.category_id, categories.category_name, tags.tag_id, tags.tag_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 LEFT OUTER JOIN posts_tags ON posts_tags.post_id=posts.post_id LEFT OUTER JOIN tags ON posts_tags.tag_id=tags.tag_id; Because I have also to JOIN tags table :) On Fri, Jun 10, 2011 at 9:56 AM, Pepijn Van Eeckhoudt < pep...@vaneeckhoudt.net> wrote: > You could use the group_concat aggregation function to concatenate the > category titles. That way you'll only have a single row per post. > Another alternative is to simply not use CursorAdapter but write your own > work-alike instead, possibly starting from the CursorAdapter source code. > > Pepijn > > > On 10/06/2011 09:24, Emanuele Ricci 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 > > > -- > 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
