1

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
meleyal
  • 32,252
  • 24
  • 73
  • 79

2 Answers2

3
SELECT s.*
FROM archive_to_section ats
  INNER JOIN section s ON s.id=ats.section_id
WHERE ats.archive_id= @archiveID
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
3
SELECT s.* 
FROM section s INNER JOIN archive_to_section ats ON s.id = ats.section_id 
WHERE ats.archive_id = 1
Greg Dean
  • 29,221
  • 14
  • 67
  • 78
  • 1
    is it necessary to write with the abbreviations? it might be easier to follow for a beginner. thanks – meleyal Oct 16 '08 at 14:58
  • It is not required to use abbreviations (alias's) many people use them. I prefer not to. See the discussion on the pros/cons of alias's http://stackoverflow.com/questions/198196/when-to-use-sql-table-alias – Nathan Koop Oct 16 '08 at 15:15