sql query with join more than one table

Question

0.00/5 (No votes)

See more:

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 :

SQL

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

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)

Posted 17-Feb-13 21:06pm

avjabalpur

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Reza Alipour Fard · Accepted Answer · 2013-02-17T21:30:00

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