我有下表:预订
| id | book_title |
|----|------------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
教师:
| id | teacher_name |
|----|--------------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
教育阶段:
| id | education_stage_name |
|----|----------------------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
书籍与教育阶段的关系:
| book_id | education_stage_id |
|---------|--------------------|
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
书籍与教师的关系:
| book_id | teacher_id |
|---------|------------|
| 1 | 1 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
现在我想挑选与id=1
教师具有相同教育阶段的书籍所以结果表应该是这样的:
| book_id | title |
|---------|-------|
| 1 | a |
因为只有id=1
的教育阶段同时被分配给书和id=1
的老师。
这是小提琴,但我甚至不知道如何开始这个查询。有没有人对如何编写这个查询有一些想法?可以用DQL语言写吗?
这不需要任何复杂的操作,如果所有记录都始终存在,则可以在所有表上使用inner join
:
select
Book.id as book_id,
Book.book_title,
Teacher.id as teacher_id,
Teacher.teacher_name
from Book
inner join book_education_stage bes on bes.book_id = Book.id
inner join Education_Stage es on es.id = bes.education_stage_id
inner join teacher_education_stage tes on tes.book_id = Book.id
inner join Teacher on tes.teacher_id = Teacher.id
结果:
book_id | book_title | teacher_idteacher_name | ||
---|---|---|---|---|
1 | a | 1 | a | |
1 | a | 1 | a | |
1 | a | 4 | d | |
1 | a | 4 | d | |
2 | b | 3 | c | |
3 | c | 4 | d