Only return parent records that have at least one child record

How to exclude parent record that don’t have a child link to it?

4 Replies

hello, @ramonmalcolm10! Do you want to use default drizzle query builder or relational queries?

Drizzle query builder @solo

I have one-to-many relation (job with setups) 1. If you want to get only parent records that have at least one child record You can use exists operator.

  const query = db.select().from(schema.setups).where(eq(schema.setups.jobId, schema.jobs.id));

  const data = await db.select().from(schema.jobs).where(exists(query));

  const query = db.select().from(schema.setups).where(eq(schema.setups.jobId, schema.jobs.id));

  const data = await db.select().from(schema.jobs).where(exists(query));

2. If you want to get only parent records that have at least one child record with child record data You can use innerJoin.

 const data2 = await db
    .select()
    .from(schema.jobs)
    .innerJoin(schema.setups, eq(schema.setups.jobId, schema.jobs.id));

 const data2 = await db
    .select()
    .from(schema.jobs)
    .innerJoin(schema.setups, eq(schema.setups.jobId, schema.jobs.id));

ramonmalcolm10OP•11mo ago

Thanks @solo

Gaming

Programming

Only return parent records that have at least one child record