sql查询加入多个表 [英] sql query with join more than one table

问题描述

嗨...，



我的sql server join查询有问题。情况是：

i有三个表产品，ProductDescription和ImageContainer.ImageContainer包含每个产品的多个图像，主键是ProductID，我当前的查询是：

Hi...,

I have a problem with sql server join query. the situation is :
i have three tables product,ProductDescription ,and ImageContainer.ImageContainer contain more than one image of each product and primary key is ProductID and my current query is :

select pm.ProductID, pm.ProductName,pm.ProductType,pm.SalesPrice,pm.DiscountPrice,pm.SubCategoryID,
 pm.BrandName,pd.Title,ic.ImageIcon,ic.SmallImage,ic.MaxImage
 from ProductMaster pm inner join ProductDescription pd
 on pm.ProductID=pd.ProductID inner join ImageContainer ic
 on pm.ProductID=ic.ProductID

此查询是正确的，返回所有值（如果imageContainer包含四种类型的图像然后它返回ea的四行ch产品），但在某些情况下我只想要单个记录

每个产品ID



提前感谢



Amit（AV）

This query is right, return all values( like if imageContainer contain four type of image then it returns four row for each product), but in some condition i want only single record for
each productID

thanks in advance

Amit (AV)

推荐答案

嗨


如果你想要显示一行代表一个产品（带图像的产品）您必须确定哪些图像是您最喜欢的图像。



我认为波纹管代码可以帮助您：

Hi
if you want show one row for one product( a product with a image) you must determine which of images is your favorite image fore select.

I think bellow code can help you:

select	Product.ProductID	As	ID, 
	Product.ProductName	As	Name,
	Product.ProductType	As	Type,
	Product.SalesPrice	As	Price,
	Product.DiscountPrice	As	Discount,
	Product.SubCategoryID	As	SubCategory,
	Product.BrandName	As	Branch,
	Descriptions.Title	As	Description,
	ISNULL((Select	Top(1) ImageIcon
			From	ImageContainer
			Where	ProductID	=	Product.ProductID	--And
				-- Your Policy for select On image
				-- Order By	YourSelectPolicyColumnName	Desc
		) , 0)		As	Icon,
	ISNULL((Select	Top(1) SmallImage
		From	ImageContainer
		Where	ProductID	=	Product.ProductID	--And
			-- Your Policy for select On image
			-- Order By	YourSelectPolicyColumnName	Desc
		) , 0)		As	Image,
	ISNULL((Select	Top(1) MaxImage
		From	ImageContainer
		Where	ProductID	=	Product.ProductID	--And
			-- Your Policy for select On image
			-- Order By	YourSelectPolicyColumnName	Desc
		) , 0)		As	MaxImage,

	from	ProductMaster		As	Product 
			Inner Join 
		ProductDescription	As	Descriptions
			On	Product.ProductID	= Descriptions.ProductID 

这篇关于sql查询加入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！