Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Regarding Join Query Result
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/07/2013 :  10:56:27  Show Profile  Reply with Quote
I am facing problem regarding join query.I want to join more than 4 tables and retrieve result but I am little bit confused regarding the query which was i had created,it can shows result like multiple records in different row not in one row.I want to retrieve result in one row.

Here I am giving my join query

select PRD.Id AS ProductID,PRD.Name AS ProductName,PRD.ShortDescription,PRD.FullDescription,PRD.AdminComment,SA.Name AS [Type],SAO.Name AS TypeValue,PRDV.Price,PRDV.StockQuantity,PRDV.Sku from [uva].[dbo].[Product_SpecificationAttribute_Mapping] PSM inner join [uva].[dbo].[SpecificationAttributeOption] SAO on PSM.SpecificationAttributeOptionId = SAO.Id inner join [uva].[dbo].[SpecificationAttribute] SA on SAO.SpecificationAttributeId = SA.Id inner join [uva].[dbo].[Product] PRD on PSM.ProductId = PRD.Id inner join [uva].[dbo].[ProductVariant] PRDV on PRD.Id = PRDV.ProductId where PRD.Id >1

It showing result like this....

please check the result of my above query.

But I want result like this..
Product_id ProductName shortDesc,FullDesc,AdminComment,Author,ISBN,Pages,ImageURL,Price,Quantity,Sku

All in only one row how I can do this ,which join I need to Use
because it showing result in multiple rows for single product.

Please give proper suggestion to me

Thank You

Aged Yak Warrior

700 Posts

Posted - 11/07/2013 :  12:00:06  Show Profile  Reply with Quote
It looks as though you need to pivot the attribute/values.
Try to provide consumable test data in future.
Something like the following should work but with no test data the details are up to you:

WITH Attribs
	SELECT Id, Author, ISBN, Pages,Image_URL
		SELECT SAO.Id, SA.Name AS [Type], SAO.Name AS TypeValue
		FROM [uva].[dbo].[SpecificationAttribute] SA
			JOIN [uva].[dbo].[SpecificationAttributeOption] SAO
				ON SA.Id = SAO.SpecificationAttributeId
	) AS S
		FOR [Type] IN (Author,ISBN,Pages,Image_URL)
	) AS P
	PRD.Id AS ProductID
	,PRD.Name AS ProductName
	,A.Author, A.ISBN, A.Pages, A.Image_URL
FROM [uva].[dbo].[Product] PRD
	JOIN [uva].[dbo].[ProductVariant] PRDV
		ON PRD.Id = PRDV.ProductId
	JOIN [uva].[dbo].[Product_SpecificationAttribute_Mapping] PSM
		ON PRD.Id = PSM.ProductId
	JOIN Attribs A
		ON PSM.SpecificationAttributeOptionId = A.Id;

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000