Select unique rows based on condition stored in another table

Question

I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below.

# Questions Table: 
╔════════════╦═══════════════╦════════╗
║ id         ║ chapter_id    ║ weight ║
╠════════════╬═══════════════╬════════╣
║ question-1 ║ first         ║ 1      ║
║ question-2 ║ second        ║ 1      ║
║ question-3 ║ third         ║ 1      ║
║ question-4 ║ first         ║ 2      ║
║ question-5 ║ third         ║ 2      ║
╚════════════╩═══════════════╩════════╝

# Patterns Tables
╔════╦═════════════════╦════════╗
║ id ║ question_number ║ weight ║
╠════╬═════════════════╬════════╣
║ p1 ║ 1               ║ 1      ║
║ p2 ║ 2               ║ 1      ║
║ p3 ║ 3               ║ 1      ║
║ p4 ║ 4               ║ 2      ║
║ p5 ║ 5               ║ 2      ║
╚════╩═════════════════╩════════╝

# Patterns to SubChapters 
╔════════════╦═══════════════╗
║ pattern_id ║ chapter_id    ║
╠════════════╬═══════════════╣
║ p1         ║ first         ║
║ p2         ║ second        ║
║ p3         ║ third         ║
║ p4         ║ first         ║
║ p4         ║ second        ║
║ p5         ║ second        ║
║ p5         ║ third         ║
╚════════════╩═══════════════╝

Now I want to select a unique question for each matching pattern

Required Output:

╔════════════╦═════════════════╦═════════════╗
║ pattern_id ║ question_number ║ question_id ║
╠════════════╬═════════════════╬═════════════╣
║ p1         ║ 1               ║ question-1  ║
║ p2         ║ 2               ║ question-2  ║
║ p3         ║ 3               ║ question-3  ║
║ p4         ║ 4               ║ question-4  ║
║ p5         ║ 6               ║ question-5  ║
╚════════════╩═════════════════╩═════════════╝

// p1 can be from first chapter with weight = 1 (i.e. question-1)
// p2 can only be second chapter with weight = 1 (i.e. question-2)
// p3 can be from third chapter with weight = 1 (i.e. question-3)
// p4 can be from first with weight = 2 (question-4)
// p5 can be from third with weight = 2 (question-5)

// similarly if there are multiple options, it should choose randomly from one of them.

// Weight are also considered a condition and the generated question_id column should be unique

I am quite new to sql databases still learning, please let me know what can done to tackle this kind of issue? I am using postgres with drizzle orm for my quiz application.

Currently, I have tackled this situation with 100 separate sequential queries, which takes much longer and isn't suitable for running in a cloud function. Current Solution

Answer 1

I cannot help you with the exact query, but I suggest you split up the task into 2 parts:

1. Make a query joining all tables with duplicate questions.
2. Select unique questions among the results.

For the first part you can use JOIN expression. For example, you can join Patterns to Questions using question number/id. As I understand, you also want to join Questions with "Patterns to SubChapters" table using chapter id. You can combine both results into one table with UNION operator.

Once you did that, you should have a table with duplicate questions. You can pick unique questions according to your criteria with SELECT DISTINCT ON. You will specify what unique column do you want (question_id) and how to choose one row among duplicates (ORDER BY ...)

Hope that helps!