Search code examples
sqlpostgresqlmany-to-many

SQL many-to-many get all from a table

I have three tables:

  1. users
id some_user_data some_more_user_data
1 ... ...
2 ... ...
3 ... ...
  1. classrooms
id some_classroom_data some_more_classroom_data
1 ... ...
2 ... ...
3 ... ...
  1. classroom_users
id user_id classroom_id
1 1 1
2 1 3
3 2 1
4 3 1
5 3 2

I need a query that, given a user_id, can get all the data for all the classrooms that particular user is in. So, if I ran the query on the tables above given a user_id of 1, I want to get back the following result:

id some_classroom_data some_more_classroom_data
1 ... ...
3 ... ...

Thanks in advance for your help. I have been trying to wrap my mind around this SQL query for far too long.

FYI, I am using Postgres.

Solution

  • This should help

    select c.* from classroom_users cu, classrooms c
where cu.user_id = <input user id> and cu.classroom_id = c.id;