?
Solved

Selecting distinct size names order by rank

Posted on 2011-04-19
11
Medium Priority
?
361 Views
Last Modified: 2012-05-11
Select distinct tbl_MasterSizes.SizeName, tbl_SizeColorStock.ID order by tbl_SizeColorStock .Rank. I have the table structure as follow.

tbl_SizeColorStock

ID      bigint    Primary key      
ProductID      varchar(50)      
SizeID      bigint      
ColorID      bigint      
Price      decimal(18, 2)      
Stock      int      
Rank      int      
Active      bit      
            
tbl_MasterSizes

SizeID      bigint      Primary key
SizeName      varchar(100)      


Script of tables as follow

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_SizeColorStock](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [ProductID] [varchar](50) NOT NULL,
      [SizeID] [bigint] NOT NULL,
      [ColorID] [bigint] NOT NULL,
      [Price] [decimal](18, 2) NOT NULL,
      [Stock] [int] NOT NULL,
      [Rank] [int] NOT NULL,
      [Active] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_Size] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_SizeColorStock] ON
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (2, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 1, 2, CAST(23.00 AS Decimal(18, 2)), 45, 2, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (3, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 2, 3, CAST(34.00 AS Decimal(18, 2)), 34, 3, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (4, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 3, 3, CAST(34.00 AS Decimal(18, 2)), 23, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (8, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 1, 1, CAST(45.00 AS Decimal(18, 2)), 3, 4, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (9, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 1, CAST(34.50 AS Decimal(18, 2)), 23, 2, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (10, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 4, 2, CAST(44.99 AS Decimal(18, 2)), 34, 7, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (13, N'163a25a9-fa4b-48c8-b565-a6962402c7a4', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (14, N'163a25a9-fa4b-48c8-b565-a6962402c7a4', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (15, N'93e8546b-270d-4fec-b261-c4846aa3ca3a', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (16, N'93e8546b-270d-4fec-b261-c4846aa3ca3a', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (17, N'4a8e930e-a036-42ad-9b43-a6dc9f228dc8', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (18, N'4a8e930e-a036-42ad-9b43-a6dc9f228dc8', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (21, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 2, CAST(56.00 AS Decimal(18, 2)), 5, 3, 0)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (22, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 3, CAST(787.00 AS Decimal(18, 2)), 3, 5, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (23, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 4, 3, CAST(78.00 AS Decimal(18, 2)), 5, 4, 0)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (24, N'2d4e4943-0b47-4e87-b441-ffdca01e359a', 1, 1, CAST(56.00 AS Decimal(18, 2)), 34, 6, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (25, N'3e4c6ab8-bfbb-4ec6-8840-17d09d1bd959', 1, 1, CAST(67.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (26, N'c8261df1-e48f-4a3b-a77c-c092eca83711', 1, 1, CAST(23.00 AS Decimal(18, 2)), 3, 1, 1)
SET IDENTITY_INSERT [dbo].[tbl_SizeColorStock] OFF
/****** Object:  Table [dbo].[tbl_MasterSizes]    Script Date: 04/19/2011 17:42:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_MasterSizes](
      [SizeID] [bigint] IDENTITY(1,1) NOT NULL,
      [SizeName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Sizes] PRIMARY KEY CLUSTERED
(
      [SizeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_MasterSizes] ON
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (1, N'X')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (2, N'S')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (3, N'XL')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (4, N'M')
SET IDENTITY_INSERT [dbo].[tbl_MasterSizes] OFF
/****** Object:  Default [DF_tbl_SizeColorStock_Rank]    Script Date: 04/19/2011 17:42:36 ******/
ALTER TABLE [dbo].[tbl_SizeColorStock] ADD  CONSTRAINT [DF_tbl_SizeColorStock_Rank]  DEFAULT ((1)) FOR [Rank]
GO
/****** Object:  Default [DF_tbl_SizeColorStock_Active]    Script Date: 04/19/2011 17:42:36 ******/
ALTER TABLE [dbo].[tbl_SizeColorStock] ADD  CONSTRAINT [DF_tbl_SizeColorStock_Active]  DEFAULT ((1)) FOR [Active]
GO
0
Comment
Question by:Gurbirs
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35423984
Assuming that the rank is the same for the same tbl_MasterSizes.SizeName,  tbl_SizeColorStock.ID:

SELECT tbl_MasterSizes.SizeName,
            tbl_SizeColorStock.ID,
            MAX(tbl_SizeColorStock .Rank) Rank
FROM ...
GROUP BY
            tbl_MasterSizes.SizeName,
            tbl_SizeColorStock.ID
ORDER BY
          Rank
0
 

Author Comment

by:Gurbirs
ID: 35430946
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35432409
Try it this way (fix the obvious typos on xSelect):
xSELECT	s.ID,
	m.SizeName
FROM	tbl_SizeColorStock s
	INNER JOIN (
		xSELECT	SizeID,
			MIN(ID) ID
		FROM	tbl_SizeColorStock
		GROUP BY
			SizeID) sx ON s.ID = sx.ID
	INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
ORDER BY
	s.[Rank]

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Gurbirs
ID: 35439596
Suggested solution does not solve my problem. I am again attaching word file which contain different scenarios of select query.

Waiting for reply.
PremumQuery.docx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35445566
>>Suggested solution does not solve my problem.<<
Than you could not possible have tested it.  Here is a complete solution:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_SizeColorStock](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [ProductID] [varchar](50) NOT NULL,
      [SizeID] [bigint] NOT NULL,
      [ColorID] [bigint] NOT NULL,
      [Price] [decimal](18, 2) NOT NULL,
      [Stock] [int] NOT NULL,
      [Rank] [int] NOT NULL,
      [Active] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_Size] PRIMARY KEY CLUSTERED 
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON

SET IDENTITY_INSERT [dbo].[tbl_SizeColorStock] ON
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (2, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 1, 2, CAST(23.00 AS Decimal(18, 2)), 45, 2, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (3, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 2, 3, CAST(34.00 AS Decimal(18, 2)), 34, 3, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (4, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 3, 3, CAST(34.00 AS Decimal(18, 2)), 23, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (8, N'89bf49e5-e506-4e3e-bd79-bb3309ee6477', 1, 1, CAST(45.00 AS Decimal(18, 2)), 3, 4, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (9, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 1, CAST(34.50 AS Decimal(18, 2)), 23, 2, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (10, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 4, 2, CAST(44.99 AS Decimal(18, 2)), 34, 7, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (13, N'163a25a9-fa4b-48c8-b565-a6962402c7a4', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (14, N'163a25a9-fa4b-48c8-b565-a6962402c7a4', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (15, N'93e8546b-270d-4fec-b261-c4846aa3ca3a', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (16, N'93e8546b-270d-4fec-b261-c4846aa3ca3a', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (17, N'4a8e930e-a036-42ad-9b43-a6dc9f228dc8', 3, 1, CAST(45.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (18, N'4a8e930e-a036-42ad-9b43-a6dc9f228dc8', 4, 3, CAST(34.00 AS Decimal(18, 2)), 34, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (21, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 2, CAST(56.00 AS Decimal(18, 2)), 5, 3, 0)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (22, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 1, 3, CAST(787.00 AS Decimal(18, 2)), 3, 5, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (23, N'53ee5dc1-c2eb-4a63-ad97-7871b25d1f92', 4, 3, CAST(78.00 AS Decimal(18, 2)), 5, 4, 0)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (24, N'2d4e4943-0b47-4e87-b441-ffdca01e359a', 1, 1, CAST(56.00 AS Decimal(18, 2)), 34, 6, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (25, N'3e4c6ab8-bfbb-4ec6-8840-17d09d1bd959', 1, 1, CAST(67.00 AS Decimal(18, 2)), 45, 1, 1)
INSERT [dbo].[tbl_SizeColorStock] ([ID], [ProductID], [SizeID], [ColorID], [Price], [Stock], [Rank], [Active]) VALUES (26, N'c8261df1-e48f-4a3b-a77c-c092eca83711', 1, 1, CAST(23.00 AS Decimal(18, 2)), 3, 1, 1)
SET IDENTITY_INSERT [dbo].[tbl_SizeColorStock] OFF
/****** Object:  Table [dbo].[tbl_MasterSizes]    Script Date: 04/19/2011 17:42:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_MasterSizes](
      [SizeID] [bigint] IDENTITY(1,1) NOT NULL,
      [SizeName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Sizes] PRIMARY KEY CLUSTERED 
(
      [SizeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_MasterSizes] ON
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (1, N'X')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (2, N'S')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (3, N'XL')
INSERT [dbo].[tbl_MasterSizes] ([SizeID], [SizeName]) VALUES (4, N'M')
SET IDENTITY_INSERT [dbo].[tbl_MasterSizes] OFF
/****** Object:  Default [DF_tbl_SizeColorStock_Rank]    Script Date: 04/19/2011 17:42:36 ******/
ALTER TABLE [dbo].[tbl_SizeColorStock] ADD  CONSTRAINT [DF_tbl_SizeColorStock_Rank]  DEFAULT ((1)) FOR [Rank]
GO
/****** Object:  Default [DF_tbl_SizeColorStock_Active]    Script Date: 04/19/2011 17:42:36 ******/
ALTER TABLE [dbo].[tbl_SizeColorStock] ADD  CONSTRAINT [DF_tbl_SizeColorStock_Active]  DEFAULT ((1)) FOR [Active]
GO
SELECT  s.ID,
        m.SizeName
FROM    tbl_SizeColorStock s
        INNER JOIN (SELECT  SizeID,
                            MIN(ID) ID
                    FROM    tbl_SizeColorStock
                    GROUP BY SizeID
                   ) sx ON s.ID = sx.ID
        INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
ORDER BY s.[Rank]
GO
DROP TABLE [dbo].[tbl_SizeColorStock], [dbo].[tbl_MasterSizes]


OUTPUT:
ID                   SizeName
-------------------- --------------------
4                    XL
2                    X
3                    S
10                   M

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35445580
If you want a condition based on ProductID than that is easy, too (see below).  Notice how it matches your output exactly.
DECLARE @ProductID varchar(50)
SET @ProductID = '89bf49e5-e506-4e3e-bd79-bb3309ee6477'

SELECT  s.ID,
        m.SizeName
FROM    tbl_SizeColorStock s
        INNER JOIN (SELECT  SizeID,
                            MIN(ID) ID
                    FROM    tbl_SizeColorStock
                    GROUP BY SizeID
                   ) sx ON s.ID = sx.ID
        INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
WHERE	s.ProductID = @ProductID
ORDER BY s.[Rank]

OUTPUT:
ID                   SizeName
-------------------- --------------------
4                    XL
2                    X
3                    S

Open in new window

0
 

Author Comment

by:Gurbirs
ID: 35446157
When I add following query it returns nothing. I need

DECLARE @ProductID varchar(50)
SET @ProductID = '93e8546b-270d-4fec-b261-c4846aa3ca3a'

SELECT  s.ID,
        m.SizeName
FROM    tbl_SizeColorStock s
        INNER JOIN (SELECT  SizeID,
                            MIN(ID) ID
                    FROM    tbl_SizeColorStock
                    GROUP BY SizeID
                   ) sx ON s.ID = sx.ID
        INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
WHERE      s.ProductID = @ProductID
ORDER BY s.[Rank]
But I need following output
Id                   SizeName
15                  XL      
16                  M

When I add following query
DECLARE @ProductID varchar(50)
SET @ProductID = '53ee5dc1-c2eb-4a63-ad97-7871b25d1f92'

SELECT  s.ID,
        m.SizeName
FROM    tbl_SizeColorStock s
        INNER JOIN (SELECT  SizeID,
                            MIN(ID) ID
                    FROM    tbl_SizeColorStock
                    GROUP BY SizeID
                   ) sx ON s.ID = sx.ID
        INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
WHERE      s.ProductID = @ProductID
ORDER BY s.[Rank]
It returns
Id                   Size Name
10                  M      

But I should be
ID                  SizeName
9                  X      
23                  M
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35447026
Use this (just fix the obvious typos):
xSELECT	s.ID,
	m.SizeName
FROM	tbl_SizeColorStock s
	INNER JOIN (
	    xSELECT  ProductID,
		    SizeID,
		    MIN([Rank]) [Rank]
	    FROM    tbl_SizeColorStock
	    GROUP BY
		    ProductID, 
		    SizeID) sx ON s.ProductID = sx.ProductID 
				    AND s.SizeID = sx.SizeID 
				    AND s.[Rank] = sx.[Rank]
	INNER JOIN tbl_MasterSizes m ON s.SizeID = m.SizeID
WHERE   s.ProductID = @ProductID
ORDER BY
	s.[Rank]

Open in new window

0
 

Author Comment

by:Gurbirs
ID: 35448198
Thank you very much. My problem has been solved. I think I have not design the database correctly. May I take your little more valuable time? Please suggest what should be the correct design of database for the following scenario.
1)      A product (Clothing item) can have different sizes .
2)      Each size available in different colors.
3)      I have to maintain stock for size and color. Ex.  Size X  available in Black and Red color for ProductA, we have 2 pieces of Black in stock and 4 pieces of Red color in stock for Size X of ProductA .
4)      I have to give rank to sizes so that I can change the position of the size.
5)      Price can also change according to size and color.
Reference site http://asp.dev.webcreationuk.com/PremiumBuys/LadiesFleeceLinedWaterproofJacket.aspx   
0
 

Author Comment

by:Gurbirs
ID: 35448239
I am asking this because suggested query take more time than normal query. I also want to know the better ways to design the database. Please suggest.
Thanks once again.
Shall waiting for your reply.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35451150
I would create a table for each product, regardless of the size and color, and then use a cross reference table to get the size and color.  Something like this:
CREATE TABLE Products (
	ProductID integer IDENTITY(1,1) NOT NULL,
	ProductName varchar(50) NOT NULL,
	Price decimal(18, 2) NOT NULL,
	Active bit NOT NULL
	CONSTRAINT PK_ProductID PRIMARY KEY CLUSTERED (ProductID)
	)

CREATE TABLE ProductAttributes (
	ProductID integer NOT NULL,
	SizeID tinyint NOT NULL,
	ColorID tinyInt NOT NULL,
    Stock integer NOT NULL,
    [Rank] integer NOT NULL
    CONSTRAINT PK_ProductAttributes PRIMARY KEY CLUSTERED (ProductID, SizeID, ColorID)
	)

CREATE TABLE Colors (
	ColorID tinyInt NOT NULL,
	ColorName varchar(50)
	CONSTRAINT PK_Colors PRIMARY KEY CLUSTERED (ColorID)
	)

CREATE TABLE Sizes (
	SizeID tinyInt NOT NULL,
	SizeName varchar(50)
	CONSTRAINT PK_Sizes PRIMARY KEY CLUSTERED (SizeID)
	)

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question