CODEWITHSUNDEEP
mysqlsqlpostgresql
Mostafa El-Messiry
Mostafa El-Messiry

Reputation: 380

Single query to collect data from multiple tables

I have a problem collecting data from an SQL database that I designed.

This is a table of questions of different types, each type has it's own table with different columns and has the questionid as a foreign key that referenced this table as seen below.

 Column   |          Type          | Modifiers
------------+------------------------+-----------
 questionid | integer                | not null
 header     | character varying(500) |
Indexes:
    "quizquestion_pkey" PRIMARY KEY, btree (questionid)
Referenced by:
    TABLE "matchingpairs" CONSTRAINT "matchingpairs_questionid_fkey" FOREIGN KEY (questionid) REFERENCES quizquestion(questionid)
    TABLE "mcqchoices" CONSTRAINT "mcqchoices_questionid_fkey" FOREIGN KEY (questionid) REFERENCES quizquestion(questionid)
    TABLE "questionsinquiz" CONSTRAINT "questionsinquiz_questionid_fkey" FOREIGN KEY (questionid) REFERENCES quizquestion(questionid)
    TABLE "truefalsequestion" CONSTRAINT "truefalsequestion_questionid_fkey" FOREIGN KEY (questionid) REFERENCES quizquestion(questionid
)

I have another table that keeps up which question belongs to which quiz using also the questionid

   Column   |  Type   | Modifiers
------------+---------+-----------
 quizid     | integer | not null
 questionid | integer | not null
 index      | integer |
Indexes:
    "questionsinquiz_pkey" PRIMARY KEY, btree (quizid, questionid)
Foreign-key constraints:
    "questionsinquiz_questionid_fkey" FOREIGN KEY (questionid) REFERENCES quizquestion(questionid)
    "questionsinquiz_quizid_fkey" FOREIGN KEY (quizid) REFERENCES quiz(quizid)

Is there a way to collect all the different questions in one query or do I have to query on every question type, or is there something different that I can change in the database table design.

Upvotes: 0

Views: 314

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Based on your description, you can use Postgres inheritance. This is a facility where tables can be related to each other. The place to start learning about it is in the documentation.

Using inheritance, you would have a parent table called questions which defines questionId and other related columns. Then you can define multiple other tables such as matchingPairsQuestions which inherit from questions. Queries and foreign keys can then refer either to the individual "children" tables or to all of them as a single set.

Upvotes: 1

Related Questions