PostgreSQL query multiple tables using JOIN statement

Question

I'm pretty new to SQL and Postgres. I have encountered a case where I think I should use JOIN, but it looks more complicated actually and I don't know what query to use.

So i have 3 tables: students, schools and users.

users:

       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

students:

  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 |

schools:

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 |

I want to use SELECT query to get back username_repetition of a user that is a student, only if the student is assigned to a specific school. How can I achieve this? Please, let me know if you need further information about this. Thank you!

You should include your attempt at solving the problem.

Gordon Linoff
– Gordon Linoff

2020-05-23 15:57:03 +00:00
Commented May 23, 2020 at 15:57 — Gordon Linoff
– Gordon Linoff, Commented May 23, 2020 at 15:57

Anton · Accepted Answer · 2020-05-23 16:12:25Z

1

Try this:

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 = ?.

If user isn't a student or isn't linked to a school the query will return no results.

edited May 23, 2020 at 16:12

answered May 23, 2020 at 16:03

Anton

2,0361 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shawnt00 Over a year ago

Generally we would put filters in a where clause. It does make a difference with outer joins obviously.

Collectives™ on Stack Overflow

PostgreSQL query multiple tables using JOIN statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related