© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•12mo ago•
2 replies
Sparrow

how to model this query in drizzle

SELECT 
  tournaments.id, 
  tournaments.title, 
  tournaments.info, 
  tournaments.due_date, 
  tournaments.registration, 
  winner.name AS winner,
  runner_up.name AS runner_up,
  most_points.name AS most_points
FROM tournaments
INNER JOIN users AS winner ON tournaments.winner = winner.id
INNER JOIN users AS runner_up ON tournaments.runner_up = runner_up.id
INNER JOIN users AS most_points ON tournaments.most_points = most_points.id
WHERE tournaments.id = '45FobTao63vsRyc1N1';
SELECT 
  tournaments.id, 
  tournaments.title, 
  tournaments.info, 
  tournaments.due_date, 
  tournaments.registration, 
  winner.name AS winner,
  runner_up.name AS runner_up,
  most_points.name AS most_points
FROM tournaments
INNER JOIN users AS winner ON tournaments.winner = winner.id
INNER JOIN users AS runner_up ON tournaments.runner_up = runner_up.id
INNER JOIN users AS most_points ON tournaments.most_points = most_points.id
WHERE tournaments.id = '45FobTao63vsRyc1N1';


I currently have this:
  const tmp = await db
    .select({
      ...getTableColumns(tournaments_table),
      // id: tournaments_table.id,
      winner: tournaments_table.winner,
      runner_up: tournaments_table.runner_up,
      most_points: tournaments_table.most_points,
    })
    .from(tournaments_table)
    .innerJoin(users_table, eq(tournaments_table.winner, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.runner_up, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.most_points, users_table.id))
    .where(eq(tournaments_table.id, params.id));
  const tmp = await db
    .select({
      ...getTableColumns(tournaments_table),
      // id: tournaments_table.id,
      winner: tournaments_table.winner,
      runner_up: tournaments_table.runner_up,
      most_points: tournaments_table.most_points,
    })
    .from(tournaments_table)
    .innerJoin(users_table, eq(tournaments_table.winner, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.runner_up, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.most_points, users_table.id))
    .where(eq(tournaments_table.id, params.id));


but I get an error saying:
Error: Alias "users" is already used in this query
Error: Alias "users" is already used in this query
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

How to express this query in Drizzle?
Drizzle TeamDTDrizzle Team / help
3y ago
How to migrate this betterSqlite3 query to drizzle
Drizzle TeamDTDrizzle Team / help
2y ago
How to do this in drizzle schema?
Drizzle TeamDTDrizzle Team / help
3y ago
Is there a way to create this query in drizzle?
Drizzle TeamDTDrizzle Team / help
16mo ago