I want make table 4 like this one in SQL server 2005

I Have 3 table Like this:

Table 1:

Name = Tbl_Mian_code

M_Code

Master_Code

Master_Name

Main_Type

Main_Gruop

Main_Txt

1001

1001

Bank

01

01

1002

1002

Cash

01

02

1003

1003

Good's

02

01

Table 2:

Name = Tbl_Mian_code

Id_2

Main_Code

Sub_Code

Master_Code

Master_Name

Sub_Txt

1

1001

0001

10010001

Bank_Visa

4

1001

0002

10010002

Bank_234

5

1002

0001

10020001

Cash_1

6

1002

0002

10020002

Cash_2

7

1003

0001

10030001

Nokia

9

1003

0002

10030002

Samsung

Table 3:

Name = Tbl_Sub_T

Id_3

Sub_Master

Code

Sub_T_Code

Master_Code

Master_Name

Sub_T_Txt

1

10030001

0001

100300010001

Nokia 6600

2

10030001

0002

100300010002

Nokia 6630

3

10030001

0003

100300010003

Nokia 6230

4

10030002

0001

100300020001

Samsung T 60

5

10030002

0002

100300020002

Samsung E500

6

10030002

0003

100300020003

Samsung E700

I want make table 4 like this one in SQL server 2005 or 2000

Master_Code

Master_Name

1001

Bank

10010001

Bank_Visa

10010002

Bank_234

1002

Cash

10020001

Cash_1

10020002

Cash_2

1003

Good's

10030001

Nokia

100300010001

Nokia 6600

100300010002

Nokia 6630

100300010003

Nokia 6230

10030002

Samsung

100300020001

Samsung T 60

100300020002

Samsung E500

100300020003

Samsung E700

The sub code is unique but depends on main code. As you see master Code is unique.

How I can generate it by function in SQL.

And How to make table 4

Say me how.

Tanks For your help.

Make table 1, 2, 3 by this code & help me to make table 4 with your code.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Sub_Code_Tbl_Mian_code]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Sub_Code] DROP CONSTRAINT FK_Tbl_Sub_Code_Tbl_Mian_code
GO

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

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

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

CREATE TABLE [dbo].[Tbl_Mian_code] (
[Main_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([Main_code]) ,
[Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Main_Type] [char] (2) COLLATE Arabic_CI_AS NULL ,
[Main_Gruop] [char] (2) COLLATE Arabic_CI_AS NULL ,
[Main_Txt] [text] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tbl_Sub_T] (
[Id_3] [int] NOT NULL ,
[Sub_Master_Code] [char] (8) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_T_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([sub_master_code] + [sub_T_code]) ,
[Master_Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_T_Txt] [ntext] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tbl_Sub_Code] (
[Id_2] [int] IDENTITY (1, 1) NOT NULL ,
[Main_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([main_code] + [sub_code]) ,
[Master_Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_Txt] [ntext] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tbl_Mian_code] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Mian_code] PRIMARY KEY CLUSTERED
(
[Main_Code]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tbl_Sub_T] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Sub_T] PRIMARY KEY CLUSTERED
(
[Id_3]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tbl_Sub_Code] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Sub_Code] PRIMARY KEY CLUSTERED
(
[Id_2]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tbl_Sub_Code] WITH NOCHECK ADD
CONSTRAINT [IX_Tbl_Sub_Code] UNIQUE NONCLUSTERED
(
[Master_Code]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tbl_Sub_Code] ADD
CONSTRAINT [FK_Tbl_Sub_Code_Tbl_Mian_code] FOREIGN KEY
(
[Main_Code]
) REFERENCES [dbo].[Tbl_Mian_code] (
[Main_Code]
)
GO

looks like a simple union view. . .
caveat emptor (not checked, just hacked)!!!

select [master_code], [master_name]
from
(

select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]

|||

Hi .

That's realy work . tanks.

But How I can use it in progaming?

If it was table I can make a relation. and load it in combo box.

whit this code how I can use it?

I use VB.NET 2005 Beta 2 ( Non express )

Thanks for your help.

|||well, from a .Net 1.1 viewpoint as I am not yet doing much with 2.0.

given that sql is set to the above select statement and aCon is an initialized SQLConnection:

Dim da as SQLDataAdapter = new SQLDataAdapter(sql, aCon)
dim ds as DataSet = new DataSet()
da.Fill(da)|||Hi
Tnanks for your answer.

But let me ask in other way .

how I can save code you write as a view?

I khow how to load a view or table in windows form.

but I can't save your code as View!!!|||create view MyViewName as

[enter select sql here]

great examples in the help files|||

Hi
I use this code to make view

create view My_Code as

select [master_code], [master_name]
from
(

select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]
and I got this Erorr

Server: Msg 1033, Level 15, State 1, Procedure MyViewName, Line 26
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

|||ORDER BY clause is only relevant in the outer-most query that returns the results. You can use it in derived tables or sub-queries or views or inline TVFs with TOP clause. In your example, you have to query the view with the appropriate ORDER BY clause otherwise there is no guarantee that the results will be returned in the order requested even if you include TOP in the view definition. This may have worked in the past with SQL Server 2000 but it will not when you upgrade to SQL Server 2005.|||

Hi
Thanks for your answer.

If it is not possble in SQL server 2005 what I Can do in SQL server 2005?
what I can do In SQL server 2000?

I Can chenge My program to Use SQL server 2000. but I need a view that showed in my frist post.

How I can make it . I need that table . the way of making it is not major.

by the way . I need to use that table very much in my program . ( may be 1 time evry min ) but my table 1 . 2 . 3 dont change very much. ( once when a Good's add or . . . )

the performance is my main problem too.

|||

You can use the view without any problem. You just need to remove the ORDER BY clause and put in the SELECT statement that is used to query the view. This will work fine both in SQL Server 2000 and 2005.

|||
WOW It's realy works.

I cant belive it .

thanks . thanks For All.

I got my Answer . And I am very happy.|||I think you can do:

create view My_Code as

select TOP 100 PERCENT [master_code], [master_name]
from
(

select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]

|||Oh yeah. . .

I want to say:
'Way To Go!!! Including the script to create the tables you are working with!!!'

Thats the way to do it if you really want help!

cheers and good luck!