连接具有外键ID的2个表 [英] Joining 2 tables with foreign key id

问题描述

我有一个2类系统，基本上我想做的是我有2个表top_category和bottom_category，我创建了侧边栏，该边栏将使用sql查询列出所有产品.有没有一种方法可以在一个sql查询中提取top_category和bottom_category数据，并按top_category的外键ID对bottom_category进行排序，所以当我在列表中循环它们时，它们最终会出现在正确的嵌套中?

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?

这是我的桌子，

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
);

这是我的产品表，所以当我单击bottom_category链接时，我希望它列出链接到bottom_category_id的产品:

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
);

推荐答案

您可以编写类似

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

但是，如果您有很大的表，则只需忽略第3个范式，然后将类别名称添加到product表中即可.但是只有，如果您有包含类别的大型表格.

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 添加ORDER BY

这篇关于连接具有外键ID的2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！