Thanks for your message. Yes of course, there's a wrong table alias here: JOIN actor AS f
It should be: JOIN actor AS a I added the aliases for readability purposes. The actual query generated by jOOQ doesn't alias the tables Best Regards, Lukas On Mon, Feb 21, 2022 at 11:04 PM '[email protected]' via jOOQ User Group <[email protected]> wrote: > Maybe I missed something, but when I try the PostgreSQL example from the > otherwise great "No More MultipleBagFetchException Thanks to Multiset > Nested Collections" blog post, I get the following error: > > [Code: 0, SQL State: 42P01] ERROR: missing FROM-clause entry for table "a" > Position: 156 [Script position: 156 - 168] > > I've tried with DBVisualizer and DBeaver. > > The faulty SQL: > > SELECT > f.title, > ( > SELECT coalesce( > jsonb_agg(jsonb_build_array("v0", "v1")), > jsonb_build_array() > ) > FROM ( > SELECT > a.first_name AS "v0", > a.last_name AS "v1" > FROM film_actor AS fa > JOIN actor AS f > USING (actor_id) > WHERE fa.film_id = f.film_id > ) AS "t" > ) AS actors, > ( > SELECT coalesce( > jsonb_agg(jsonb_build_array("v0")), > jsonb_build_array() > ) > FROM ( > SELECT c.name AS "v0" > FROM film_category AS fc > JOIN category AS c > USING (category_id) > WHERE fc.film_id = f.film_id > ) AS "t" > ) AS categories > FROM film AS f > ORDER BY f.title > > 'a' isn't declared somewhere and the actor table doesn't contain column > film_id ... > > Kind regards > Dominik > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/0fca15d0-b12b-471a-9258-44b9310d7482n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/0fca15d0-b12b-471a-9258-44b9310d7482n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6ED8mbDVAL4zYSFqP6v8Qt_5Ta%3DKVyX_qyXRPR1Odtpg%40mail.gmail.com.
