SQL Server上的父子关系查询4表实现 [英] Parent Child relation query in SQL Server on 4 table realtion
本文介绍了SQL Server上的父子关系查询4表实现的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE T_ProductCategories
(
ProductCategoryID BIGINT IDENTITY ( 1 ,< span class =code-digit> 1 ) PRIMARY KEY ,
ProductCategoryName NVARCHAR ( 50 ),
ProductCategoryDescription NVARCHAR ( 200 ),
ProductCategoryActivationDate DATE ,
ProductCategoryExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR ( 20 ),
CreatedON DATE ,
UpdatedBy < span class =code-ke yword> Varchar ( 20 ),
UpdatedON DATE
)
CREATE TABLE T_ProductSubCategories
(
ProductSubCategoryID BIGINT IDENTITY ( 1 , 1 ) PRIMARY KEY ,
ProductSubCategoryName < span class =code-keyword> NVARCHAR ( 50 ),
ProductSubCategoryDescription NVARCHAR ( 200 ),
ProductSubCategoryActivationDate DATE ,
ProductSubCategoryExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR < /秒pan>( 20 ),
CreatedON DATE ,
UpdatedBy Varchar ( 20 ),
UpdatedON DATE ,
ProductCategoryID BIGINT FOREIGN KEY REFERENCES T_ProductCategories(ProductCategoryID)
)
CREATE 表 T_MainProducts
(
MainProductID BIGINT IDENTITY ( 1 , 1 ) PRIMARY KEY ,
MainProductName NVARCHAR ( 50 ) ,
MainProductDescription NVARCHAR ( 200 ),
MainProductActivationDate DATE ,
MainProductExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR ( 20 ),
CreatedON DATE ,
UpdatedBy Varchar ( 20 ),
UpdatedON DATE ,
ProductSubCategoryID BIGINT FOREIGN KEY REFERENCES T_ProductSubCategories(ProductSubCategoryID)
)
CREATE TABLE T_SubProducts
(
SubProductID BIGINT IDENTITY ( 1 , 1 ) PRIMARY KEY ,
SubProductName NVARCHAR ( 50 ),
SubProductDescription NVARCHAR ( 200 ),
SubProductPicture NVARCHAR ( 500 ),
ProductBrand NVARCHAR ( 20 ),
SubProductActivationDate DATE ,
SubProductExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR ( 20 ),
CreatedON DATE ,
UpdatedBy Varchar ( 20 ),
UpdatedON DATE ,
MainProductID BIGINT FOREIGN KEY REFERENCES T_MainProducts(MainProductID)
)
i已经创建了这些表我希望显示父子关系,就像我调用父类一样,它会显示所有可见的孩子礼物
解决方案
你几乎已经完成了。只需使用内部联接
SELECT T_ProductCategories.ProductCategoryID,T_ProductCategories.ProductCategoryName ,T_ProductSubCategories.ProductSubCategoryName,
T_MainProducts.MainProductName,T_SubProducts.SubProductName
FROM T_ProductCategories INNER JOIN
T_ProductSubCategories ON T_ProductCategories.ProductCategoryID = T_ProductSubCategories.ProductCategoryID INNER JOIN
T_MainProducts ON T_ProductSubCategories.ProductSubCategoryID = T_MainProducts.ProductSubCategoryID INNER JOIN
T_SubProducts < span class =code-keyword> ON T_MainProducts.MainProductID = T_SubProducts.MainProductID
其中 T_ProductCategories.ProductCategoryID = 1 跨度>
CREATE TABLE T_ProductCategories
(
ProductCategoryID BIGINT IDENTITY(1,1) PRIMARY KEY ,
ProductCategoryName NVARCHAR(50),
ProductCategoryDescription NVARCHAR(200),
ProductCategoryActivationDate DATE ,
ProductCategoryExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR(20),
CreatedON DATE ,
UpdatedBy Varchar(20),
UpdatedON DATE
)
CREATE TABLE T_ProductSubCategories
(
ProductSubCategoryID BIGINT IDENTITY(1,1) PRIMARY KEY ,
ProductSubCategoryName NVARCHAR(50),
ProductSubCategoryDescription NVARCHAR(200),
ProductSubCategoryActivationDate DATE ,
ProductSubCategoryExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR(20),
CreatedON DATE ,
UpdatedBy Varchar(20),
UpdatedON DATE,
ProductCategoryID BIGINT FOREIGN KEY REFERENCES T_ProductCategories(ProductCategoryID)
)
CREATE TABLE T_MainProducts
(
MainProductID BIGINT IDENTITY(1,1) PRIMARY KEY ,
MainProductName NVARCHAR(50),
MainProductDescription NVARCHAR(200),
MainProductActivationDate DATE ,
MainProductExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR(20),
CreatedON DATE ,
UpdatedBy Varchar(20),
UpdatedON DATE,
ProductSubCategoryID BIGINT FOREIGN KEY REFERENCES T_ProductSubCategories(ProductSubCategoryID)
)
CREATE TABLE T_SubProducts
(
SubProductID BIGINT IDENTITY(1,1) PRIMARY KEY ,
SubProductName NVARCHAR(50),
SubProductDescription NVARCHAR(200),
SubProductPicture NVARCHAR(500),
ProductBrand NVARCHAR(20),
SubProductActivationDate DATE ,
SubProductExpiryDate DATE ,
ISActive BIT ,
CreatedBy VARCHAR(20),
CreatedON DATE ,
UpdatedBy Varchar(20),
UpdatedON DATE,
MainProductID BIGINT FOREIGN KEY REFERENCES T_MainProducts(MainProductID)
)
i have crated these tables i want to show the parent child relation like when i call the parent like productID it will show the all posible child present
解决方案
you have done almost already. just use inner join
SELECT T_ProductCategories.ProductCategoryID, T_ProductCategories.ProductCategoryName, T_ProductSubCategories.ProductSubCategoryName, T_MainProducts.MainProductName, T_SubProducts.SubProductName FROM T_ProductCategories INNER JOIN T_ProductSubCategories ON T_ProductCategories.ProductCategoryID = T_ProductSubCategories.ProductCategoryID INNER JOIN T_MainProducts ON T_ProductSubCategories.ProductSubCategoryID = T_MainProducts.ProductSubCategoryID INNER JOIN T_SubProducts ON T_MainProducts.MainProductID = T_SubProducts.MainProductID where T_ProductCategories.ProductCategoryID = 1
这篇关于SQL Server上的父子关系查询4表实现的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文