Help with Qury Please? - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Qury Please?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-09-21 : 21:38:48

Hi All,

I have 2 tables. A Products Table and a Product Attributes Table.

A simple Inner Join Brings back all the product with All Attributes. However what I want is only 1 row per SubID and only the values from the Minimum AttributeID rows from each SubID.

Or in other words I want ONLY the Min AttributeID's per SubID in the rowset. There are 3 attribute SubID's total so only 3 rows returned. Sample Desired Rowset


AttrID    AttName    AttValue
1         Shirt      555555
6         Collar     ddffff
9         Stripe     ae97f2

Here is some data to create the tables.


CREATE TABLE [dbo].[xTestProducts](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Price] [int] NOT NULL CONSTRAINT [DF_xTestProducts_Price]  DEFAULT ((0)),
	[Prestige] [int] NOT NULL CONSTRAINT [DF_xTestProducts_Prestige]  DEFAULT ((0)),
	[Gender] [tinyint] NOT NULL CONSTRAINT [DF_xTestProducts_Gender]  DEFAULT ((0)),
	[Active] [bit] NOT NULL CONSTRAINT [DF_xTestProducts_Active]  DEFAULT ((1)),
	[Deleted] [bit] NOT NULL CONSTRAINT [DF_xTestProducts_Deleted]  DEFAULT ((0)),
	[Inventory] [bigint] NOT NULL CONSTRAINT [DF_xTestProducts_Inventory]  DEFAULT ((10000)),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_xTestProducts_CreateDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_xTestProducts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[xTestProductAttributes](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ProductID] [bigint] NOT NULL,
	[SubID] [bigint] NOT NULL,
	[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Value] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_xTestProductAttributes_Active]  DEFAULT ((1)),
	[Deleted] [bit] NOT NULL CONSTRAINT [DF_xTestProductAttributes_Deleted]  DEFAULT ((0)),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_xTestProductAttributes_CreateDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_xTestProductAttributes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Declare @ProductID bigint

-- Insert into Products Table
Insert Into xTestProducts (Name,Description,Price,Prestige,Gender)
Values ('Polo Shirt','A very cheap Polo Shirt',1200,12,1)

-- Get the ID
Select @ProductID = Scope_Identity()

-- Insert into ProductAttributes Table
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,1,'Shirt','555555')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,1,'Shirt','996633')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,1,'Shirt','ff66ff')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,1,'Shirt','6666cc')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,1,'Shirt','ddffff')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,2,'Collar','ddffff')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,2,'Collar','ffffff')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,2,'Collar','cccccc')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,3,'Stripe','ae97f2')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,3,'Stripe','dcdc06')
Insert Into xTestProductAttributes(ProductID,SubID,Name,Value)
Values(@ProductID,3,'Stripe','fefefe')

Select *
From xTestProducts

Select *
From xTestProductAttributes

Select *
From xTestProducts a
Inner Join xTestProductAttributes b
On a.ID = b.PRoductID

Drop Table xTestProductAttributes
Drop Table xTestProducts

JBelthoff
› As far as myself... I do this for fun!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:20:19

here is your query:


SELECT B.ID AS [ProductId], B.Name AS [ProductName], A.ID [AttrID], A.Name AS [AttrName], A.Value [AttrValue]
FROM
    (
       SELECT ID , Name, Value, Rank() OVER (Partition By SubID order by ID ASC) AS RankInGroup FROM xTestProductAttributes 
    )A
INNER JOIN xTestProducts B
        ON A.ProductID = B.ID
WHERE RankInGroup IN(1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources