Newby Help 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)

Newby Help Please

Author

Topic

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 10:28:34

Hi all,

I don't even know if this is possible... but the (simplified) problem is below...

tblProducts has ProdID, CadID, Name
tblVariants has VariantID, ProdID, VariantText

A Product can have multiple Variants.

I want to return a list of Products with the details from the first Variant

Thanks in advance...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 10:58:43

SELECT a.ProdID,a.CadID,a.name,b.variantId,b.VariantText
FROM
tblProducts a
INNER JOIN
(
SELECT ProdID,VariantID,VariantText
,'RowNumber' = row_number() OVER(Partition by Prodid order by variantid)
FROM tblVariants
) b
ON
a.prodid = b.prodid
WHERE b.rownumber = 1

Jim

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 11:09:48

Thanks for the speedy reply Jim....

But, the query only returns 1 record... I need to return all Products (including the details from the first Variant)

[I've never even heard of the OVER keyword - another demonstration of "the more you know, the more you know you don't know!"]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 11:21:01

My sample data returned multiple rows for me. Could you post the DDLs (the CREATE TABLEs) for your tables and sample data for each? I was just guessing at what things looked like. You'll still be doing something like above. RANK and ROW_NUMBER are very useful for this type of query.

Jim

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 11:41:58

Jim - thanks VERY MUCH for your help!

Tables are as follows:

USE [dbShop]
GO
/****** Object:  Table [dbo].[tblProducts]    Script Date: 09/20/2007 16:23:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblProducts](
	[ProdID] [int] IDENTITY(1,1) NOT NULL,
	[SubCatID] [int] NULL,
	[Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[QtyInStock] [int] NULL,
	[Price] [money] NULL,
	[ShippingRateID] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SpecialOffer] [bit] NULL,
	[OfferPrice] [money] NULL,
	[OfferText] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OfferUntil] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [dbShop]
GO
/****** Object:  Table [dbo].[tblVariants]    Script Date: 09/20/2007 16:25:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblVariants](
	[VariantID] [int] IDENTITY(1,1) NOT NULL,
	[ProdID] [int] NULL,
	[VariantText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Stock] [int] NULL,
	[Price] [money] NULL,
	[OfferPrice] [money] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

As for sample data...

INSERT INTO [dbShop].[dbo].[tblProducts] (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Hammer', '0', '0.00', '1', 'This is a hammer')
INSERT INTO [dbShop].[dbo].[tblProducts] (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Screwdriver', '0', '0.00', '1', 'This is a screwdriver')
INSERT INTO [dbShop].[dbo].[tblProducts] (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Spanner', '0', '0.00', '1', 'This is a spanner')

INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('1', 'Hammer 8oz', '2', '5.99', '0.00')
INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('1', 'Hammer 10oz', '2', '6.99', '0.00')
INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('2', 'Posidrive', '2', '1.99', '0.00')
INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('2', 'Flat head', '2', '1.99', '0.00')
INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('3', '13mm', '2', '5.99', '0.00')
INSERT INTO [dbShop].[dbo].[tblVariants] (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('3', '14mm', '2', '5.99', '0.00')

The required result from the data above would be:
tblProducts.ProdID, tblProducts.Name, tblVariants.VariantID, tblVariants.VariantText, tblVariants.Price, tblVariants.Stock
but only 1 row for each product Hammer, Screwdriver & Spanner.

Thanks again Jim!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 11:56:59

And you want the 8oz Hammer, Posidrive Screwdriver, and 13mm spanner right?

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 11:58:40

That's correct.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:02:27

SELECT prods.prodID,prods.Name
,vars.variantID,vars.VariantText,vars.Price,vars.stock
FROM
#tblProducts prods
INNER JOIN
(select prodid,variantid,varianttext,price,stock
,'RowNumber' = row_number() over(partition by prodid order by variantid)
from #tblVariants
) vars

ON
prods.prodid = vars.prodid
WHERE vars.rownumber = 1

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 12:15:12

In the SQL Management Studio I get the error "The OVER SQL construct or statement is not supported", then the query returns 1 row again.

So I put the query into ASP code - the query again only returns 1 row.

Back in the SQL Management Studio, I selected selected File/New/Query and typed query in the editing window instead - it parsed ok, but when run I got "Invalid object name 'tblProducts'" error.

Sometimes I love SQL... then there's the rest of the time!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:25:03

You are in SQL 2005 right?

CREATE TABLE #tblVariants (
[VariantID] [int] IDENTITY(1,1) NOT NULL,
[ProdID] [int] NULL,
[VariantText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Stock] [int] NULL,
[Price] [money] NULL,
[OfferPrice] [money] NULL
)

GO
SET ANSI_PADDING OFF

INSERT INTO #tblProducts (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Hammer', '0', '0.00', '1', 'This is a hammer')
INSERT INTO #tblProducts (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Screwdriver', '0', '0.00', '1', 'This is a screwdriver')
INSERT INTO #tblProducts (SubCatID ,Name, QtyInStock, Price, ShippingRateID, Description) VALUES ('1', 'Spanner', '0', '0.00', '1', 'This is a spanner')

INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('1', 'Hammer 8oz', '2', '5.99', '0.00')
INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('1', 'Hammer 10oz', '2', '6.99', '0.00')
INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('2', 'Posidrive', '2', '1.99', '0.00')
INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('2', 'Flat head', '2', '1.99', '0.00')
INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('3', '13mm', '2', '5.99', '0.00')
INSERT INTO #tblVariants (ProdID, VariantText, Stock, Price, OfferPrice) VALUES ('3', '14mm', '2', '5.99', '0.00')

SELECT * FROM #tblVariants
SELECT * FROM #tblProducts
tblProducts.ProdID, tblProducts.Name, tblVariants.VariantID, tblVariants.VariantText, tblVariants.Price, tblVariants.Stock

SELECT prods.prodID,prods.Name
,vars.variantID,vars.VariantText,vars.Price,vars.stock
FROM
#tblProducts prods
INNER JOIN
(select prodid,variantid,varianttext,price,stock
,'RowNumber' = row_number() over(partition by prodid order by variantid)
from #tblVariants
) vars

ON
prods.prodid = vars.prodid
WHERE vars.rownumber = 1

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 12:27:31

Yep (just re-checked)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:32:05

I'm a bit stumped, since the above code produces this
prodID Name variantID VariantText Price stock
1 Hammer 1 Hammer 8oz 5.99 2
2 Screwdriver 3 Posidrive 1.99 2
3 Spanner 5 13mm 5.99 2

When you were in the Query Window, were you in the same database as your tables are in?

Jim

jezza
Starting Member

12 Posts

Posted - 2007-09-20 : 12:43:16

Jim - you are a star!!

Your query works - I made a mistake implementing!!

Thanks VERY much for your help today!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:56:58

YAY! You're welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources