I have the following tables:

subjects

id	title
subject_1	Subject 1
subject_2	Subject 2
subject_3	Subject 3

tests

id	title
test_1	Test 1
test_2	Test 2

quizzes

id	title
quiz_1	Quiz 1

questions

id	title
question_1	Question 1

And junction tables:

subjects_tests

subject	test
subject_1	test_1
subject_2	test_2

subjects_quizzes

subject	quiz
subject_3	quiz_1

tests_questions

test	question
test_1	question_1
test_2	question_1

quizzes_questions

quiz	question
quiz_1	question_1

I know the question id. As a result I want to get:

question.title	test.title	quiz.title	subject.title
Question 1	Test 1	null	Subject 1
Question 1	Test 2	null	Subject 2
Question 1	null	Quiz 1	Subject 3

How can this be achieved?

CodePudding user response：

select q.Title as questionTitle,
       t.Title as testTitle,
       null    as quizTitle,
       s.Title as subjectTitle
from questions q
         inner join tests_questions tq on q.id = tq.question
         inner join tests t on t.id = tq.test
         inner join subjects_tests st on t.id = st.test
         inner join subjects s on s.id = st.subject
-- where q.id = 'question_1'
union all
select q.Title as questionTitle,
       null    as testTitle,
       qz.Title as quizTitle,
       s.Title as subjectTitle
from questions q
         inner join quizzes_questions qq on q.id = qq.question
         inner join quizzes qz on qz.id = qq.quiz
         inner join subjects_quizzes sq on qz.id = sq.quiz
         inner join subjects s on s.id = sq.subject
-- where q.id = 'question_1'
;

DBFiddle demo

CodePudding user response：

SELECT qn.title AS question.title,
       t.title AS test.title, 
       qz.title AS quiz.title, 
       s.title AS subject.title
FROM questions AS qn
  LEFT JOIN tests_questions AS tq ON qn.id = tq.question
  LEFT JOIN tests AS t ON tq.test = t.id
  LEFT JOIN quizzes_questions AS qq ON qn.id = qq.question
  LEFT JOIN quizzes AS qz ON qz.id = qq.quiz
  LEFT JOIN subjects_tests st AS t ON t.id = st.test
  LEFT JOIN subjects AS s ON s.id = st.subject