jay k
jay k
PostsComments
DTDrizzle Team
Created by jay k on 1/30/2024 in #help
How to use `not` to avoid returning results where ID is in a specific join table?
Howdy! I have this SQL query: 
SELECT * FROM drills WHERE organization_id = 1 AND id NOT IN (SELECT drill_id FROM practice_drills WHERE team_id = 2)
SELECT * FROM drills WHERE organization_id = 1 AND id NOT IN (SELECT drill_id FROM practice_drills WHERE team_id = 2)
I'm trying to figure out how to convert it to Drizzle and apply it to an another query as a subquery, but I'm having some problems. Here's what I came up with: 
    const subQuery = db
      .select({ drillId: practiceDrills.drillId })
      .from(practiceDrills)
      .where(eq(practiceDrills.teamPracticeId, params.teamPracticeId));

    const data = await db.query.drills.findMany({
      where: and(eq(drills.organizationId, params.organizationId), not(eq(drills.id, subQuery))),
    });
    const subQuery = db
      .select({ drillId: practiceDrills.drillId })
      .from(practiceDrills)
      .where(eq(practiceDrills.teamPracticeId, params.teamPracticeId));

    const data = await db.query.drills.findMany({
      where: and(eq(drills.organizationId, params.organizationId), not(eq(drills.id, subQuery))),
    });
When I run this, I receive this error: 
more than one row returned by a subquery used as an expression
more than one row returned by a subquery used as an expression
How can I resolve this?
3 replies