Search code examples
sqlmany-to-manyrelational

How to write a SQL statement which gets results via a relationship table? (many to many)

I have 3 tables (archive has many sections, section (may) belong to many archives):

  • archive

    • id PK
    • description

  • archive_to_section

    • archive_id PK FK
    • section_id PK FK

  • section

    • id PK
    • description

What would the SQL look like to list all the sections that belong a certain archive id?

I am just learning SQL. From what I've read it sounds like I would need a join, or union? FYI I'm using postgres.

[Edit] This is the answer from gdean2323 written without aliases:

SELECT section.* 
FROM section 
INNER JOIN archive_to_section 
ON section.id = archive_to_section.section_id 
WHERE archive_to_section.archive_id = $this_archive_id
Solution 
  • SELECT s.* 
FROM section s INNER JOIN archive_to_section ats ON s.id = ats.section_id 
WHERE ats.archive_id = 1