JPA中具有多对多关系的三个联接表查询 [英] Three joined tables query with many-to-many relationship in JPA

问题描述

共有三个表:Hospital，Medical_Service和Language_Service， 医院可以提供医疗服务和语言服务.因此，存在两个多对多关系.

There are three tables: Hospital, Medical_Service and Language_Service, Hospital can provide medical service and language service. So there are two many-to-many relationships.

ERD图片

现在我想按三个条件搜索医院:邮政编码，医疗和语言，如何编写此SQL.

Now I want to search hospitals by three conditions: Postcode, Medical and Language, how can I write this SQL.

推荐答案

我认为您的SQL可能看起来像这样:

I think your SQL might look something like this:

SELECT * FROM Hospital WHERE Postcode = 3000 AND
Hospital_id IN
    (SELECT Hospital_id FROM Hospital_Medical hm
    INNER JOIN Medical_Service m ON hm.Medical_id = m.Medical_id
    where Medical_name = 'Emergency') AND
Hospital_id IN
(SELECT Hospital_id FROM Hospital_Language hl
    INNER JOIN Language_Service l ON hl.Language_id = l.Language_id
    where Language_name = 'English')

这篇关于JPA中具有多对多关系的三个联接表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！