使用外键id连接2个表

Question

i have a 2 category system, basically what i want to do is i have 2 tables, top_category and bottom_category, i have created my sidebar which will list all the products using sql query. is there a way i can pull the top_category and bottom_category data in one sql query and have the bottom_category sorted by the foreign key id of top_category so when i loop them in a list they end up in the right nest?

Here are my tables,

CREATE TABLE top_category (
  id INT PRIMARY KEY,
  NAME VARCHAR(100) 
);

CREATE TABLE bottom_category (
  id INT PRIMARY KEY,
  NAME VARCHAR(100) ,
  top_category_id INT REFERENCES top_category
);

And here is my products table, so when i click on a bottom_category link i want it to list the products linked to the bottom_category_id's:

create table product (
  id int primary key,
  name varchar(100) ,
  bottom_category_id int references bottom_category
);

Answer 1

You could write something like

SELECT product.*, bottom_category.name, top_category.name
FROM product
LEFT JOIN bottom_category ON bottom_category.id = product.bottom_category_id 
LEFT JOIN top_category ON top_category.id = bottom_category.top_category_id
ORDER BY top_category.id,bottom_category.id

But if you have really big tables then just forget about 3nd normal form and add names for categories into product table. But only if you have really big tables with categories.

UPD Add ORDER BY