Querry Help

Question

Querry Help

BuilderBits

SSCertifiable

Points: 6071
More actions
July 25, 2005 at 9:42 am

#102660

With my procedure below it will run but will not return any results. This procudure was working fine until i added a this portion (((((((((((((((((((((((((((
SprocsTable.AttributeCategoryColorID, SprocsTable.AttributeCategoryTapeColorID,
SprocsTable.AttributeCategoryRoutlessID, SprocsTable.StandardHeadrailLiftOptionID , SprocsTable.[HeadrailLiftOption-LeftID] ,
SprocsTable.[HeadrailLiftOptin-Right],SprocsTable.[HeadrailLiftOption-Center],SprocsTable.AttributeCategoryFabricID,
SprocsTable.AttributeCategoryBlackOutFabricID,SprocsTable.AttributeCategoryMoonlightFabricID,
SprocsTable.AttributeCategoryRoomDarKeningID,SprocsTable.AttributeCategoryRomanFabricID,SprocsTable.AttributeCategoryRoomsID,
SprocsTable.AttributeCategoryQuantyID
))))))))))))))))))))))))))))
The new portion that i have added is all of the ids from a new table that i have made called SprocsTable. The reason that i have created this table is because i need to fill up a container with the values from this table so that i can cast them to another page. The page that the SprocTables' values are going to is a very sticky page that a user has many options to choose from, (per the selection that has been made from the menu from the previous page,,,this is the menu that will hold all of the values that i am tring to get to the container at this time) Getting this querry working would save alot of code writing on the vb.net end..
Any suggerstion Please....
If i have left something out please let me know..
Erik...
=============MYSproc............................./
ALTER PROCEDURE Fill_Datalist_From_Top_Menu_On_ByCompany_Page_Blind_Special_Shapes_Comfortex
@IDCompanyProductCategory int ,
@IDCompany int,
@IDProduct int
AS
SELECT DISTINCT Company.CompanyID, Company.CompanyName,Product.ProductName,
Product.ProductID,Product.ProductImage, Product.ProductDescription,
SprocsTable.AttributeCategoryColorID, SprocsTable.AttributeCategoryTapeColorID,
SprocsTable.AttributeCategoryRoutlessID, SprocsTable.StandardHeadrailLiftOptionID , SprocsTable.[HeadrailLiftOption-LeftID] ,
SprocsTable.[HeadrailLiftOptin-Right],SprocsTable.[HeadrailLiftOption-Center],SprocsTable.AttributeCategoryFabricID,
SprocsTable.AttributeCategoryBlackOutFabricID,SprocsTable.AttributeCategoryMoonlightFabricID,
SprocsTable.AttributeCategoryRoomDarKeningID,SprocsTable.AttributeCategoryRomanFabricID,SprocsTable.AttributeCategoryRoomsID,
SprocsTable.AttributeCategoryQuantyID
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
INNER JOIN
SprocsTable
ON Product.ProductID = SprocsTable.ProductID
WHERE
Company.CompanyID = @IDCompany
AND
Product
.CompanyProductCategoryID = @IDCompanyProductCategory
AND
Product
.ProductID = @IDProduct
AND
Product
.CompanyProductCategoryName = 'SheerWeave'
AND
Product
.ProductGroupID = '2'
=================================Tables...............

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Product_Company]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Product] DROP CONSTRAINT FK_Product_Company
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_M_Stacking_Chart_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[M_Stacking_Chart] DROP CONSTRAINT FK_M_Stacking_Chart_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NoOtherOptions_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NoOtherOptions] DROP CONSTRAINT FK_NoOtherOptions_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductAttribute_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductAttribute] DROP CONSTRAINT FK_ProductAttribute_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductRestrictions_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductRestrictions] DROP CONSTRAINT FK_ProductRestrictions_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductSpecialOptions_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductSpecialOptions] DROP CONSTRAINT FK_ProductSpecialOptions_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reviews_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reviews] DROP CONSTRAINT FK_Reviews_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_RollEaseRollerSystemInformation_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RollEaseRollerSystemInformation] DROP CONSTRAINT FK_RollEaseRollerSystemInformation_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SprocsTable_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SprocsTable] DROP CONSTRAINT FK_SprocsTable_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Vertical_Headrail_Info_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Vertical_Headrail_Info] DROP CONSTRAINT FK_Vertical_Headrail_Info_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Vertical_Louver_Info_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Vertical_Louver_Info] DROP CONSTRAINT FK_Vertical_Louver_Info_Product
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Product]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SprocsTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SprocsTable]
GO
CREATE TABLE [dbo].[Company] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[DepartmentID] [int] NOT NULL ,
[ProductGroupID] [int] NOT NULL ,
[CompanyName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyLogo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyAddress] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyCity] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyProvince] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyCountry] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyZipCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyPhone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyFax] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyEmail] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyRep] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyRepPhone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyRepEmail] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyWebMaster] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyWebmasterPhone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyWebMasterEmail] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyDateEnter] [datetime] NULL ,
[CompanyUserName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyPhoto] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyShortDescription] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[ProductID] [int] NOT NULL ,
[ProductName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyProductCategoryID] [int] NULL ,
[CompanyProductCategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductGroupID] [int] NOT NULL ,
[DepartmentID] [int] NULL ,
[CompanyID] [int] NOT NULL ,
[ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductDescription] [nvarchar] (170) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductActive] [bit] NULL ,
[ProductFeatured] [bit] NULL ,
[ProductFeaturedStart] [datetime] NULL ,
[ProductFeaturedEnd] [datetime] NULL ,
[ProductSaleStart] [datetime] NULL ,
[ProductSaleEnd] [datetime] NULL ,
[ProductSalePrice] [money] NULL ,
[UnitCostPerFoot] [money] NULL ,
[UnitCostPerSection] [money] NULL ,
[UnitCostPerPiece] [money] NULL ,
[BulkCost] [money] NULL ,
[BulletOne] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletTwo] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletThree] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletFour] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletFive] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletSix] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletSeven] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletEight] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletNine] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BulletTen] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SprocsTable] (
[SprocsTable] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NOT NULL ,
[AttributeCategoryColorID] [int] NULL ,
[AttributeCategoryTapeColorID] [int] NULL ,
[AttributeCategoryRoutlessID] [int] NULL ,
[StandardHeadrailLiftOptionID] [int] NULL ,
[HeadrailLiftOption-LeftID] [int] NULL ,
[HeadrailLiftOptin-Right] [int] NULL ,
[HeadrailLiftOption-Center] [int] NULL ,
[AttributeCategoryFabricID] [int] NULL ,
[AttributeCategoryBlackOutFabricID] [int] NULL ,
[AttributeCategoryMoonlightFabricID] [int] NULL ,
[AttributeCategoryRoomDarKeningID] [int] NULL ,
[AttributeCategoryRomanFabricID] [int] NULL ,
[AttributeCategoryRoomsID] [int] NULL ,
[AttributeCategoryQuantyID] [int] NULL ,
[SprocsTableGroupID] [int] NULL
) ON [PRIMARY]
GO
Dam again!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BuilderBits SSCertifiable Points: 6071 More actions · Answer 1

Since i pasted that first amout of script, i am going to past this also, so that it will make a little more since. IF i can even do that..

What my main goal here is to pull the attributes based on the attribuitecategory, that the sprocstable columnids cast.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductAttribute_Attribute]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[ProductAttribute] DROP CONSTRAINT FK_ProductAttribute_Attribute

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Attribute_AttributeCategory]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Attribute] DROP CONSTRAINT FK_Attribute_AttributeCategory

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Attribute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Attribute]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AttributeCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[AttributeCategory]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductAttribute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ProductAttribute]

GO

CREATE TABLE [dbo].[Attribute] (

[AttributeID] [int] IDENTITY (1, 1) NOT NULL ,

[AttributeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[AttributeCategoryID] [int] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[AttributeCategory] (

[AttributeCategoryID] [int] IDENTITY (1, 1) NOT NULL ,

[AttributeCategoryName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[AttributeCategoryNameID] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ProductAttribute] (

[ProductID] [int] NOT NULL ,

[AttributeID] [int] NOT NULL

) ON [PRIMARY]

GO

Dam again!

Johan Bijnens SSC Guru Points: 135553 More actions · Answer 2

I cannot find anything tricky in the query, so I guess it's a data-problem.

It contains only INNER-joins. Are you sure the data always should be returned.

Try changing the last innerjoin to a left-join and study the results and test why there is no match !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

BuilderBits

SSCertifiable

Points: 6071

July 26, 2005 at 6:14 am

#577125

Ok.

Dam again!