star Structure Select - 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)

star Structure Select

Author

Topic

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-24 : 02:06:18

Hi
i'm working on a factory output system and i have very serious problem let me explain about it:
i have a star structure which it tell me what kindand how much stuf is need to prduce a product and it may goes down to 5 level for example formaking A it need (2 of B1(B1*2)) and (1 of B2) and for making B1 need (2 of C1) and (1 of B2) and for making C1 need E1 and again for maiking E1 need (2 of F1)
so it goes to 5 level if we count A ,so now I want to know how many stuf is needed for a i have solve this but Know i want to now my each stuff is used by which node (and how many) for example in this product(goes up from down (node to root))2 of f1 is used in e1
,E1 used in c1 so for making B1 we Need (2 of c1 AND 2 of E1)
so the query must tel me for all request(contain several product)
how many stuf is needed and in which product is used please mail me i confused in this section mail me and make me free of it javad.nikoo@yahoo.comjavad.nikoo@yahoo.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-24 : 04:16:26

hi,

please post some DDL and desired output.

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-24 : 06:38:43

hi,
as I told It Has Five Level(it will be save in five Tabel)
Table 1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRD_SGRP_SH_Kala](
[SGRP_ID] [char](12) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[SGRP_Local_ID] [char](8) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[SGRP_Kala_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[SGRP_Anb_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[SGRP_Com_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
CONSTRAINT [PK_PRD_SGRP_SH_Kala] PRIMARY KEY CLUSTERED
(
[SGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PRD_SGRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_SGRP_SH_Kala_INV_Kala] FOREIGN KEY([SGRP_Kala_ID])
REFERENCES [dbo].[INV_Kala] ([KALA_ID])
GO
ALTER TABLE [dbo].[PRD_SGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_SGRP_SH_Kala_INV_Kala]

Table 2:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRD_GRP_SH_Kala](
[GRP_ID] [char](12) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[GRP_Local_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[SGRP_ID] [char](12) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[GRP_Com_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[GRP_Anb_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[GRP_Kala_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[GRP_Unit_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[GRP_Count] [decimal](18, 3) NULL,
CONSTRAINT [PK_PRD_GRP_SH_Kala] PRIMARY KEY CLUSTERED
(
[GRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_GRP_SH_Kala_INV_Kala] FOREIGN KEY([GRP_Kala_ID])
REFERENCES [dbo].[INV_Kala] ([KALA_ID])
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_GRP_SH_Kala_INV_Kala]
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_GRP_SH_Kala_INV_Unit] FOREIGN KEY([GRP_Unit_ID])
REFERENCES [dbo].[INV_Unit] ([Unit_ID])
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_GRP_SH_Kala_INV_Unit]
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_GRP_SH_Kala_PRD_SGRP_SH_Kala] FOREIGN KEY([SGRP_ID])
REFERENCES [dbo].[PRD_SGRP_SH_Kala] ([SGRP_ID])
GO
ALTER TABLE [dbo].[PRD_GRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_GRP_SH_Kala_PRD_SGRP_SH_Kala]

Table 3:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRD_ZGRP_SH_Kala](
[ZGRP_ID] [char](13) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[ZGRP_Local_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[GRP_ID] [char](12) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_Com_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_Anb_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_Kala_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_Unit_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_Count] [decimal](18, 3) NULL,
CONSTRAINT [PK_PRD_ZGRP_SH_Kala] PRIMARY KEY CLUSTERED
(
[ZGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_ZGRP_SH_Kala_INV_Kala] FOREIGN KEY([ZGRP_Kala_ID])
REFERENCES [dbo].[INV_Kala] ([KALA_ID])
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZGRP_SH_Kala_INV_Kala]
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_ZGRP_SH_Kala_INV_Unit] FOREIGN KEY([ZGRP_Unit_ID])
REFERENCES [dbo].[INV_Unit] ([Unit_ID])
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZGRP_SH_Kala_INV_Unit]
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] WITH NOCHECK ADD CONSTRAINT [FK_PRD_ZGRP_SH_Kala_PRD_GRP_SH_Kala] FOREIGN KEY([GRP_ID])
REFERENCES [dbo].[PRD_GRP_SH_Kala] ([GRP_ID])
GO
ALTER TABLE [dbo].[PRD_ZGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZGRP_SH_Kala_PRD_GRP_SH_Kala]

Table 4:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRD_DGRP_SH_Kala](
[DGRP_ID] [char](13) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[DGRP_Local_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZGRP_ID] [char](13) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_Com_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_Anb_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_Kala_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_Unit_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_Count] [decimal](18, 3) NULL,
CONSTRAINT [PK_PRD_DGRP_SH_Kala] PRIMARY KEY CLUSTERED
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_DGRP_SH_Kala_INV_Kala] FOREIGN KEY([DGRP_Kala_ID])
REFERENCES [dbo].[INV_Kala] ([KALA_ID])
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_DGRP_SH_Kala_INV_Kala]
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_DGRP_SH_Kala_INV_Unit] FOREIGN KEY([DGRP_Unit_ID])
REFERENCES [dbo].[INV_Unit] ([Unit_ID])
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_DGRP_SH_Kala_INV_Unit]
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_DGRP_SH_Kala_PRD_ZGRP_SH_Kala] FOREIGN KEY([ZGRP_ID])
REFERENCES [dbo].[PRD_ZGRP_SH_Kala] ([ZGRP_ID])
GO
ALTER TABLE [dbo].[PRD_DGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_DGRP_SH_Kala_PRD_ZGRP_SH_Kala]

Table 5:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRD_ZDGRP_SH_Kala](
[ZDGRP_ID] [char](13) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[ZDGRP_Local_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[DGRP_ID] [char](13) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZDGRP_Com_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZDGRP_Anb_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZDGRP_Kala_ID] [char](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZDGRP_Unit_ID] [char](2) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
[ZDGRP_Count] [decimal](18, 3) NULL,
CONSTRAINT [PK_PRD_ZDGRP_SH_Kala] PRIMARY KEY CLUSTERED
(
[ZDGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_COM_Anbar] FOREIGN KEY([ZDGRP_Anb_ID])
REFERENCES [dbo].[COM_Anbar] ([ANB_ID])
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_COM_Anbar]
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_INV_Kala] FOREIGN KEY([ZDGRP_Kala_ID])
REFERENCES [dbo].[INV_Kala] ([KALA_ID])
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_INV_Kala]
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] WITH CHECK ADD CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_PRD_DGRP_SH_Kala] FOREIGN KEY([DGRP_ID])
REFERENCES [dbo].[PRD_DGRP_SH_Kala] ([DGRP_ID])
GO
ALTER TABLE [dbo].[PRD_ZDGRP_SH_Kala] CHECK CONSTRAINT [FK_PRD_ZDGRP_SH_Kala_PRD_DGRP_SH_Kala]

------------------
as you see these are the structure of my tables and table 1 is product and other are stuff of that product

now What I Want is this
company has several request(several product(diffrent))
query must tell me how many stuff is needed to produce requested product and the stuf is used in this and this is posible if one stuf is used in two level exp:in level 1 and 3 its exactly like a star graph you must imagine each product is a graph and we must come from node to root but a thing which is so important is this it must be checked in all graph
please post your E-mail for me to explain more And sen Image for you
Thanks my Friend

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-24 : 13:42:45

i'm still a bit confused.
i've sent you email.

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 14:16:50

quote:
Originally posted by slimt_slimt

i've sent you email.

Really?

I never would had dared...

PBUH

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-24 : 14:20:39


select * 
from dbo.PRD_ZDGRP_SH_Kala as t1
left join dbo.PRD_DGRP_SH_Kala as t2
on t1.DGRP_ID = t2.DGRP_ID
left join dbo.PRD_ZGRP_SH_Kala as t3
on t2.ZGRP_ID = t3.ZGRP_ID
left join dbo.PRD_GRP_SH_Kala as t4
on t3.GRP_ID = t4.GRP_ID
left join dbo.PRD_SGRP_SH_Kala as t5
on t4.SGRP_ID = t5.SGRP_ID
where
t1.ZDGRP_ID = ''

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 14:43:33

quote:

Originally posted by slimt_slimt


select * 
from dbo.PRD_ZDGRP_SH_Kala as t1
left join dbo.PRD_DGRP_SH_Kala as t2
on t1.DGRP_ID = t2.DGRP_ID
left join dbo.PRD_ZGRP_SH_Kala as t3
on t2.ZGRP_ID = t3.ZGRP_ID
left join dbo.PRD_GRP_SH_Kala as t4
on t3.GRP_ID = t4.GRP_ID
left join dbo.PRD_SGRP_SH_Kala as t5
on t4.SGRP_ID = t5.SGRP_ID
where
t1.ZDGRP_ID = ''

Wow...was it that simple????

With his kind of explanation it sounded more tough than rocket science...

PBUH

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-25 : 04:59:34

i have send you an email slimt_slimt
with doc file

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-25 : 05:02:04

this query just chek node in last level we have node in other level and whit this query we refused to chek otheres this just return the last node
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources