need help with sql tables - IMPORTANT

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)

need help with sql tables - IMPORTANT

Author

Topic

asp__developer
Posting Yak Master

108 Posts

Posted - 2009-10-01 : 16:31:49

i am developing a website in ASP.NET(VB) there is a option in this to display records of the table in gridview :

In my database i have following table:

ProductTable:

ProductID ProductName ManufacNo. CategoryID SubCatID

1 Laptop A-1233 1 2

2 Keyboard C-345 2 3

CategoryTable:

CategoryID CatName

1 Computer

2 Accessories

SubCategoryTable

SubCatID SubName

1 Good

2 Bad

3 Medium

ManufacTable

ManfID ManufacNo. ManufacName

1 A-1233 Dell

2 C-345 Intel

TechnicalSpecsTable

TechID Sort Desc ManufacNo.

1 1 101 key with wireless A-1233

2 1 wi-fi with no keys D-101

3 2 17'' screen with keyboard A-1233

4 2 105 keys with screen C-345

Let me explain what's going on in these table: a product comes in Category and then subCategory and techspecs are linked with ManufacNo. and each product will have different ManufacNo. and each product has got Number of TechnicalSpecs. like a product Laptop can have 2 techspecs while keyboard can have one 1 techspecs that is why i have given a sort column in techspecs table

Now i would like to display products in GRIDVIEW but whenever i try to write query etc it gives me the result with product having number of techspecs but each line repeats the product name for each different techspecs (hope you understood)

i would like to display in gridview (Product ID, ProductName, TechSpec_Desc) and in 1 column it should display product id, in other column it should display product name and in third column it should display all the techspec_Desc related to that product)

please help me with sql command or anything...please guide me

First of all let me give you info about Primary Keys of my tables :

ProductTable:-> ProductID , CategoryTable:->CategoryID, SubCategoryTable->SubCatID, ManufacTable->ManufacNo., TechnicalSpecsTable->TechID )
so till now i have used :

SELECT ProductTable.ProductID, ProductTable.ProductName, TechnicalSpecsTable.Desc

From Category_Table INNED JOIN ProductTable ON CategoryTable.CategoryID = ProductTable.CategoryID INNER JOIN

ManufacTable ON ProductTable.ManufacNo = ManufacTable.ManufacNo. INNED JOIN

SubCategoryTable ON ProductTable.SubCatID = SubCategoryTable.SubCatID INNED JOIN

TechnicalSpecsTable ON ManufacTable.ManufacNo. = TechnicalSpecsTable.ManufacNo. INNER AND

ProductTable.ManufacNo. = TechnicalSpecsTable.ManufacNo.

Where (ProductTable.CategoryID = 1) AND (ProductTable.SubCatID = 1)

I dont know if i am wrong of right but this is what i want to display in GridView

ProductID ProductName Desc (FromTechSpecTable)

1 Laptop 101 key with wireless

17'' screen with keyboard

I hope now you understood what i want to do and how
PLEASE GUIDE ME

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 21:51:24

Hi!

You are receiving multiple values for a single product because of the 1 to many relationship that can exist in the JOIN's to your other tables.

You have 1 of 2 options that I can think of.

1. Explore the what options may be available to you to GROUP the multiple associated records to 1 product using the Gridview control or similar data object; and then you can list a single product per record with the capability of possibly expanding to view all associated attributes. Try Googling "GRIDVIEW CONTROL and GROUPING and ASP.NET" or some combination of that

2. Concatenate your multiple associated records to a single product in its own field using T-SQL.

A simple approach that you may want to consider would be something like this:

SELECT DISTINCT ProductId, ProductName, D.*
FROM edw.dbo.Products p1
CROSS APPLY ( SELECT cast(houseid as varchar) + ','
FROM edw.dbo.Products p2
WHERE p2.productId = p1.productId
ORDER BY ProductName
FOR XML PATH('') ) D ( Houses )
WHERE ProductID = 20321

This code would return a single record for the productid in the first column with a comma-delimited list of associated houseid's.

Because of the multiple joins you've posted it would involve too much work to transform your code but this should point you in the right direction to get started.

Good Luck!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-02 : 13:21:54

Can you set up your data in a consumable format? If so, then we can probably help you more. Unless Mivey's information is enough for you.

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources