SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

nitinjagtap64
Starting Member

India
1 Posts

Posted - 11/07/2013 :  10:56:27  Show Profile  Reply with Quote
Hello,
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
Nitin

Ifor
Aged Yak Warrior

571 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
AS
(
	SELECT Id, Author, ISBN, Pages,Image_URL
	FROM
	(
		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
	PIVOT
	(
		MAX(TypeValue)
		FOR [Type] IN (Author,ISBN,Pages,Image_URL)
	) AS P
)	
SELECT
	PRD.Id AS ProductID
	,PRD.Name AS ProductName
	,PRD.ShortDescription
	,PRD.FullDescription
	,PRD.AdminComment
	,A.Author, A.ISBN, A.Pages, A.Image_URL
	,PRDV.Price
	,PRDV.StockQuantity
	,PRDV.Sku 
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  
 New 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.03 seconds. Powered By: Snitz Forums 2000