CODEWITHSUNDEEP
androidsqliteandroid-sqlite
sebap123
sebap123

Reputation: 2685

SQLite Concat and JOIN of records

I have a problem with proper DB query handling in my app and to be honest I don't know what I should do more to make it work.

My DB looks like this

BooksTable
-_id
-title
-author

RentsBookTable
-_id
-userId
-bookId
-date

UsersTable
-_id
-name

GenresTable
-_id
-name

BooksGenresTable
-bookId
-genreId

Everything from my DB I want to display inside a ListView.

I am taking everything with this clause:

BooksTable 
LEFT OUTER JOIN RentsBookTable ON(RentsBookTable.bookId=BooksTable._id)   
LEFT OUTER JOIN UsersTable ON(RentsBookTable._id=UsersTable._id) 
LEFT OUTER JOIN BooksGenresTable ON(BooksGenreTable.bookId=BooksTable._id) 
LEFT OUTER JOIN GenresTable ON(GenresTable._id=BooksGenresTable.genreId)

I want each row display title of book, the user name who rented it and the genres separated by comma.

Everything except the last part - concatenating and separating with comma of genres I am doing now correctly with displayed clause. Now books with more genres are duplicated. I've been trying 

"GROUP_CONCAT(" + GebresTable.name + ",', ') AS " + "genresConcat"

Unfortunately I am unable to achieve my goal. With this concat I am always getting only one record with all genres concatenated and displayed.

Can someone please tell me how I can achieve my goal.

Upvotes: 0

Views: 124

Answers (1)

ILovemyPoncho
ILovemyPoncho

Reputation: 2792

try with this, to show only genres that are different:

GROUP_CONCAT(DISTINCT genre) AS genre

You will also need to group the genre with GROUP BY after the where clause to show all the records that match.

Upvotes: 1

Related Questions