SQL query multi table selection

Question

I have 3 tables,
- Section table that defines some general item sections.
- Category table -> has a "section" column (foreign key).
- Product table -> has a "category" column (foreign key).

I want to get all products that belong to X section.

How can I do it?

select from select?

Answer 1

select s.section, p.*
from section s
inner join category c on c.section = s.section
inner join product p on p.category = c.category
where s.section = 'section1'

Answer 2

select p.*
from Product p
join Category c on p.CategoryId = c.Id
join Section s on c.SectionId = s.Id
where s.Id = @val

Answer 3

Select 
   prod.*
FROM
   Product prod
   INNER JOIN Category cat ON prod.category = cat.id
   INNER JOIN Section sec ON cat.section = sec.id
WHERE
   sec.id = X

Answer 4

Lots of same answers here. For some reason, though, all of them are joining the Section table which is (likely) not necessary.

select
  p.*

from
  Product    p,
  Category   c

where
  p.category_id = c.id and
  c.section_id = 123
;

---

Explicit ANSI JOIN syntax per @nemiss's request:

select
  p.*

from Product    p

join Category   c
  on c.id = p.category_id
 and c.section_id = 123
;

---

Possible reason to include Section table: Selecting products based on Section name (instead of ID).

select
  p.*

from Product    p

join Category   c
  on c.id = p.category_id

join Section    s
  on s.id = c.section_id
 and s.name = 'Books'
;

If doing this, you'll want to make sure Section.name is indexed

alter table Product add index name;