DDL
CREATE TABLE [dbo].[Status](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Status] [nvarchar](10) NULL,
[OpenedByID] [nvarchar](15) NULL,
[OpenedDate] [datetime] NULL,
CONSTRAINT [Cause_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] 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
CREATE TABLE [dbo].[Fix](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Description] [nvarchar](4000) NULL,
CONSTRAINT [Fix_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] 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
CREATE TABLE [dbo].[Measure](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[OverallPlan] [nvarchar](4000) NULL,
CONSTRAINT [Measure_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] 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
CREATE TABLE [dbo].[Statement](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Title] [nvarchar](100) NULL,
[Happening] [nvarchar](4000) NULL,
CONSTRAINT [Statement_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] 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
CREATE TABLE [dbo].[Cause](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[CausativeID] [smallint] NOT NULL,
[CauseDesc] [nvarchar](4000) NULL,
[ShortPCDesc] [nvarchar](100) NULL,
CONSTRAINT [ID_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] 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
THe select statement
Select C.CausativeID,F.Description,M.OverallPlan,S.Title, ST.Status
from Statement S left join Cause C on C.ID = S.ID
left join Measure M on S.ID = M.Id
left join Fix F on S.ID = F.ID
left join Status ST on S.ID = ST.ID
** the causative iD can have multiple ID's associated to the same ID from STatus, but i only want to check if there is an ID
i want to add a column on status that can be set to Status when the Status.Status is NULL or has no value
or as Cause when there null or no value in Cause.CausativeID ans STatus.Status have value
or as Fix when F.Description has no value and Cause.CausativeID ans STatus.Status have value
or as Status when ST. Status has no value and Cause.CausativeID ans STatus.Status,and F.Description have values
thanks