September 27, 2012 at 11:27 pm
hi every body i have two table Troofe and MoozeTroofe that relation between them is 1(Troofe) to many(MoozeTroofe).in MoozeTroofe i have info that maybe it come from one of this table Fone Or Floor OR none of them write some info about it.in Troofe Table i Can just Define That It related to which Table.how can i give relation to them???????
this is query of This Tables.
--Troofe Table
CREATE TABLE [dbo].[Troofe](
[TroofeID] [tinyint] IDENTITY(1,1) NOT NULL,
[TroofeName] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_Troofe] PRIMARY KEY CLUSTERED
(
[TroofeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Troofe] UNIQUE NONCLUSTERED
(
[TroofeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--MoozeTroofe Table
CREATE TABLE [dbo].[MoozeTroofe](
[CodeNemone] [int] NOT NULL,
[MoozeNameID] [smallint] NOT NULL,
[TroofeID] [tinyint] NOT NULL,
[EhdaKonande] [nvarchar](50) NULL,
[TaksidermiKonande] [nvarchar](50) NULL,
[PersianNameGone] [nvarchar](150) NOT NULL,
[ScienceNameGone] [nvarchar](150) NULL,
[Photographer] [nvarchar](50) NULL,
[MahaleJamavari] [nvarchar](250) NULL,
[GoneImg] [varbinary](max) NULL,
[TroofeSabt] [datetime] NOT NULL,
[TroofeEdit] [datetime] NULL,
CONSTRAINT [PK_MoozeTroofe] PRIMARY KEY CLUSTERED
(
[CodeNemone] 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].[MoozeTroofe] WITH CHECK ADD CONSTRAINT [FK_MoozeTroofe_MoozeName] FOREIGN KEY([MoozeNameID])
REFERENCES [dbo].[MoozeName] ([MoozeNameID])
GO
ALTER TABLE [dbo].[MoozeTroofe] CHECK CONSTRAINT [FK_MoozeTroofe_MoozeName]
GO
ALTER TABLE [dbo].[MoozeTroofe] WITH CHECK ADD CONSTRAINT [FK_MoozeTroofe_Troofe] FOREIGN KEY([TroofeID])
REFERENCES [dbo].[Troofe] ([TroofeID])
GO
ALTER TABLE [dbo].[MoozeTroofe] CHECK CONSTRAINT [FK_MoozeTroofe_Troofe]
GO
--Fone Table
CREATE TABLE [dbo].[Fone](
[FoneID] [int] IDENTITY(100,1) NOT NULL,
[EndUserID] [nvarchar](15) NOT NULL,
[FoneFamilyID] [smallint] NOT NULL,
[CitiesID] [tinyint] NOT NULL,
[InternationalStateID] [tinyint] NOT NULL,
[IranStateID] [tinyint] NOT NULL,
[ScienceName] [nvarchar](250) NOT NULL,
[PersianName] [nvarchar](250) NOT NULL,
[EnglishName] [varchar](250) NOT NULL,
[ZadAvar] [bit] NOT NULL,
[Mohajer] [bit] NOT NULL,
[Andemik_NimeAndemik] [bit] NOT NULL,
[Mohajem_Boomi] [bit] NOT NULL,
[FoneSabt] [datetime] NOT NULL,
[FoneEdit] [datetime] NULL,
CONSTRAINT [PK_Fone] PRIMARY KEY CLUSTERED
(
[FoneID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Floor Table
CREATE TABLE [dbo].[Floor](
[FloorID] [int] IDENTITY(1,1) NOT NULL,
[FormRosyeshID] [tinyint] NULL,
[ZistgahTypeID] [tinyint] NULL,
[EndUserID] [nvarchar](15) NULL,
[PersianName] [nvarchar](50) NULL,
[ScienceName] [nvarchar](50) NULL,
[JamAvariKonande] [nvarchar](50) NULL,
[ShenasayeKonande] [nvarchar](50) NULL,
[FloorMokhtasat] [varbinary](4000) NULL,
[FloorImg] [varbinary](max) NULL,
[TarikhSabt] [datetime] NULL,
[EditFloor] [datetime] NULL,
CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED
(
[FloorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
September 28, 2012 at 7:38 am
You need to add FoneID & FloorID to MoozeTroofe.
Allow NULL values, & only populate them as required - i.e. when there is a join.
Join to Fone & Floor using LEFT OUTER JOINs
CREATE TABLE [dbo].[MoozeTroofe](
[CodeNemone] [int] NOT NULL,
[MoozeNameID] [smallint] NOT NULL,
[TroofeID] [tinyint] NOT NULL,
[EhdaKonande] [nvarchar](50) NULL,
[TaksidermiKonande] [nvarchar](50) NULL,
[PersianNameGone] [nvarchar](150) NOT NULL,
[ScienceNameGone] [nvarchar](150) NULL,
[Photographer] [nvarchar](50) NULL,
[MahaleJamavari] [nvarchar](250) NULL,
[GoneImg] [varbinary](max) NULL,
[TroofeSabt] [datetime] NOT NULL,
[TroofeEdit] [datetime] NULL,
[FoneID] [int] NULL,
[FloorID] [int] NULL,
CONSTRAINT [PK_MoozeTroofe] PRIMARY KEY CLUSTERED
(
[CodeNemone] 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply