Create Relation Between Three Tables in DataSet

Question

0.00/5 (No votes)

See more:

Hello

I'm in Creating Dynamic Menu control and this menu will get the Data From three tables , so i need to Create Relation Between The Three Tables in The DataSet to make the XmlDataSource use it to supply the Menu control with Data

the tables is Category (as parent - top level item in the menu)

CREATE TABLE [dbo].[Category] (

            [CategoryID] [int]  PRIMARY KEY IDENTITY ,

            [CategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

--

CategorySub Table ( as sub Item )

CREATE TABLE [dbo].[CategorySub] (

            [SubCategoryID] [int] NOT NULL ,

            [CategoryIDToSub] [int] NULL ,

            [SubCategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [RcatID] [int] NULL ,

            [RsubID] [int] NULL ,

            [Rsub2ID] [int] NULL ,

            [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]
-----

CategorySub2 (As second level of sub - sub sub item)



CREATE TABLE [dbo].[CategorySub2] (

            [Sub2CategoryID] [int] NOT NULL ,

            [SubCategoryID] [int] NULL ,

            [Sub2CategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [RcatID] [int] NULL ,

            [RsubID] [int] NULL ,

            [Rsub2ID] [int] NULL ,

            [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

hope to supply me with the right Relation

Thanks

Posted 19-May-11 6:22am

ehab_developer

Updated 19-May-11 6:25am

Not Active

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Answer 1 · 2011-05-19T06:32:00

Solution 1

You really don't need three tables for this

A table such as this would allow an infinite number of levels

CREATE TABLE
ID INT PRIMARY KEY IDENTITY,
ParentID INT NULL
NAME [nvarchar] (500) NOT NULL

The data would be like this

1, null, Main
2, 1, Child1
3, 1, Child2
4, 2, SubChild1

for this hierarchy

Main
> Child1
>> SubChild1
> Child2

Posted 19-May-11 6:32am

Not Active

Comments

ehab_developer 19-May-11 19:07pm

The problem is i'm working in exist Data Base Contain Data so i'm tied with this Tables

ehab_developer 20-May-11 13:29pm

The output:

1- Table Category (The Items) :CategoryID , CategoryName with Table CategorySub (Sub Item) :SubCategoryID , CategoryIDToSub , SubCategoryName

The Relation in column CategoryID = column CategoryIDToSub ,to show the items in the subs of every item

2- Table CategorySub (Sub Item) SubCategoryID , CategoryIDToSub , SubCategoryName with Table CategorySub2 (Sub Sub Item) SubCategoryID , Sub2CategoryName

The Relation in Column SubCategoryID in table CategorySub = Column SubCategoryID in table CategorySub2 , to show the sub items and the Sub Sub item of every sub item

- i think we can ignore Column CategoryIDToSub in Table CategorySub in the Firest Relation and work with Column SubCategoryID in both Relations

Thanks

Create Relation Between Three Tables in DataSet

1 solution

Solution 1

Add your solution here

Preview 0

Create Relation Between Three Tables in DataSet

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us