在奇怪的情况下使用JOIN吗?

我对SQL和Postgres很陌生。我遇到了一种情况,我认为我应该使用JOIN,但实际上它看起来更复杂,而且我不知道要使用什么查询。

所以我有3个表:学生,学校和用户。

用户:

       Column        |          Type          | Collation | Nullable |              Default              
---------------------+------------------------+-----------+----------+-----------------------------------
 id                  | bigint                 |           | not null | nextval('users_id_seq'::regclass)
 public_id           | uuid                   |           | not null | uuid_generate_v4()
 first_name          | character varying(50)  |           | not null | 
 last_name           | character varying(50)  |           | not null | 
 username            | character varying(50)  |           | not null | 
 email               | character varying(255) |           |          | 
 password            | character varying(255) |           | not null | 
 role                | character varying(20)  |           | not null | 
 username_repetition | integer                |           |          | 1

学生们:

  Column   |  Type  | Collation | Nullable |               Default                
-----------+--------+-----------+----------+--------------------------------------
 id        | bigint |           | not null | nextval('students_id_seq'::regclass)
 class_id  | bigint |           | not null | 
 school_id | bigint |           | not null | 
 user_id   | bigint |           | not null | 

学校:

Column    |          Type          | Collation | Nullable |               Default        
--------------+------------------------+-----------+----------+-------------------------------------
 id           | bigint                 |           | not null | nextval('schools_id_seq'::regclass)
 name         | character varying(100) |           | not null | 
 short_name   | character varying(50)  |           | not null | 
 phone_number | character varying(30)  |           | not null | 
 postal_code  | character varying(30)  |           | not null | 
 adress       | character varying(255) |           | not null | 
 city         | character varying(50)  |           | not null | 
 county       | character varying(50)  |           | not null | 

我想使用SELECT查询来取回作为学生的用户的username_repetition,仅当该学生被分配到特定学校时。我该如何实现?请让我知道是否需要有关此的更多信息。谢谢!

评论
  • 婚礼礼堂
    婚礼礼堂 回复

    你好

    使用下面的查询,

    select uaername_repetition u
    inner join students s
    on (u.id = s.user_id)
    inner join schools sc
    on (s.school_id = sc.id);
    
  • 贪恋你温柔
    贪恋你温柔 回复

    尝试这个:

    SELECT username_repetition FROM users u
    INNER JOIN students std ON std.user_id = u.id
    INNER JOIN schools sch ON std.school_id = sch.id AND sch.name = 'School name'
    

    The query filters schools by name using sch.name = ?, you can filter by anything else, for example a postal code sch.postal_code = ?.

    如果用户不是学生或未链接到学校,则查询将不返回任何结果。