SQL query multi table selection
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?
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
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;
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'
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
精彩评论