I'm using Ultralite 16.0 and trying to aggregate multiple rows into one.
I have a film table and a genre table, that have a many-to-many relationship through the film_genre table.
I want a result like this:
| mFilm | idGenre |
| --------- | -----...
Welp. It was a lot easier than I thought. Just had to use List(). Hope this helps someone else!
SELECT f.nmFilm,
(SELECT LIST(g.idGenre) FROM genre g
JOIN film_genre fg on g.idGenre = fg.idGenre
WHERE fg.idFilm = f.idFilm
) AS genres FROM film f...
