How to write LEFT OUTER JOIN on the same table in jOOQ?

flesk's answer depicts nicely how this can be done with jOOQ 1.x. A self-join using aliasing is more or less equivalent to a regular join using aliasing as described in the manual:

https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/

In the upcoming version 2.0, aliasing will be made less verbose and more type-safe. Hence flesk's solution could be simplified as such:

// Type-safe table aliasing:TblCategory t1 = TBLCATEGORY.as("t1");TblCategory t2 = TBLCATEGORY.as("t2");Record record = create.select()                      .from(t1)                       // t1 and t2 give access to aliased fields:                      .leftOuterJoin(t2).on(t1.CATEGORY_ID.equal(t2.PARENT_ID))                      .where(t2.PARENT_ID.isNull())                      .and(t1.HEARTBEAT.equal("ALIVE"));

I have also described a more complex example for a self-join here:

http://blog.jooq.org/jooq-meta-a-hard-core-sql-proof-of-concept/

Maybe

SELECT *FROM food_db_schema.tblCategory AS t1WHERE t1.category_id IS NULLAND t1.heartbeat = "ALIVE";

, but are you sure t2.parent_id is both supposed to be NULL and equal to t1.category_id?

EDIT:

Then something like

Table<TblCategoryRecord> t1 = TBLCATEGORY.as("t1");Table<TblCategoryRecord> t2 = TBLCATEGORY.as("t2");Field<Integer> t1CategoryId = t1.getField(TblCategory.CATEGORY_ID);Field<String> t1Heartbeat = t1.getField(TblCategory.HEARTBEAT);Field<Integer> t2ParentId = t2.getField(TblCategory.PARENT_ID);Record record = create.select().from(t1)      .leftOuterJoin(t2).on(t1CategoryId.equal(t2ParentId))      .where(t2ParentId.isNull())      .and(t1Heartbeat.equal("ALIVE"));

depending on what the generated classes, properties and meta-model objects are called.