help with query - 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 query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-01-18 : 01:56:07

I've been trying to figure out what i'm doing wrong but I'm going in circles so maybe someeone can help me

my relavant tables are

CREATE TABLE [dbo].[attributestock](
[id] [int] IDENTITY(1,1) NOT NULL,
[productid] [int] NULL,
[optionid1value] [int] NULL,
[optionid2value] [int] NULL,
[numberinstock] [int] NULL CONSTRAINT [DF_attributestock_numberinstock] DEFAULT ((0)),
[storeid] [int] NULL,
CONSTRAINT [PK_attributestock] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[productattributes](
[id] [int] IDENTITY(1,1) NOT NULL,
[productid] [int] NULL,
[optionid] [int] NULL,
[optionvalue] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[instock] [bit] NULL CONSTRAINT [DF_productattributes_instock] DEFAULT (1),
[swatchimage] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sortorder] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[productoptiontypes](
[optionid] [int] IDENTITY(1,1) NOT NULL,
[optionname] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[storeid] [int] NULL,
[sortby] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[productoptionvalues](
[id] [int] IDENTITY(1,1) NOT NULL,
[productoptionvaluesname] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[storeid] [int] NULL,
CONSTRAINT [PK_productoptionvalues] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[productoptionvaluestoproductoptions](
[id] [int] IDENTITY(1,1) NOT NULL,
[productoptionsid] [int] NULL,
[productoptionsvaluesid] [int] NULL,
[storeid] [int] NULL,
CONSTRAINT [PK_productoptionvaluestoproductoptions] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

what I need to quey is taking a productid, optionid and a value of my first combo (which the value is the id of productattributes)
I need to get a list to show in my second combo for the option type (optionid) and it should show all options that are in stock

to show all options I can do

select * from productattributes pa where pa.productid=657 and pa.optionid=23

but I need to check if it's in stock from attributestock which has the optionid1value and optionid2value (id's from productattributes)

can anyone help me - I have going back and forth with this query and i'm not getting it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources