HI all,

I have a claim table in or database. I have seperated uncommon data from the
claims table into different class tables.
So all data that pertains to motor only type claims are removed into a motor
table, like vehicle driver make model etc. Same with other claim classes
like assets GPA marine. The claim table contains common claim details to all
classes.
The claim and class tables are related via  a primary- foreign key of
claimid
I need to be able to select a particular claim but then only select the
related class table, in a join, that is referenced by that particular claim.
So if the claim is found in the motor table, I would say simply put, end up
with
SELECT     dbo.Claim.*, dbo.Motor.*
FROM        dbo.Claim INNER JOIN
                   dbo.Motor ON dbo.Claim.ClaimID = dbo.Motor.claimid
                   where Claim.claimid = 10
But if the claim were a assets claim or it has a assets class. I would
simply:
SELECT     dbo.Claim.*, dbo.assets.*
FROM        dbo.Claim INNER JOIN
                   dbo.assetsON dbo.Claim.ClaimID = dbo.assets.claimid
                   where Claim.claimid = 12
And so on

The rule here is that one claim can only be found in one class table. So if
it is in motor is cannot be is assets or marine or gpa.
But not knowing which claim at a particular time is related to which class,
this needs to be done on the fly.

Below is whay\t I hope the needed DDL for this scenario

Thanks
Robert






CREATE TABLE [dbo].[Claim] (
[ClaimID] [int] IDENTITY (1, 1) NOT NULL ,
[ctkey] [int] NULL ,
[headerid] [int] NOT NULL ,
[cpkey] [int] NULL ,
[firstest] [Rand] NULL ,
[Total] [Rand] NULL ,
[updated] [datetime] NULL ,
[usrname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[brkref] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[DET] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Status] [tinyint] NULL ,
[peril] [int] NULL ,
[cause] [int] NULL ,
[resc] [int] NULL ,
[fault] [int] NULL ,
[polsection] [int] NULL ,
[Assid] [int] NULL ,
[rasclmnumber] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Motor] (
[claimid] [int] NOT NULL ,
[drv] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[drvid] [char] (13) COLLATE Latin1_General_CI_AS NULL ,
[veh] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[model] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[reg] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[assets] (
[claimid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[marine] (
[claimid] [int] NOT NULL ,
[Insval] [Rand] NULL ,
[ocntry] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[dcntry] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[vessel] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[gpa] (
[claimid] [int] NOT NULL ,
[Fname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Lname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Claim] WITH NOCHECK ADD
CONSTRAINT [PK_Claim] PRIMARY KEY  CLUSTERED
(
  [ClaimID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Motor] WITH NOCHECK ADD
CONSTRAINT [PK_Motor] PRIMARY KEY  CLUSTERED
(
  [claimid]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[assets] WITH NOCHECK ADD
CONSTRAINT [PK_assets] PRIMARY KEY  CLUSTERED
(
  [claimid]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[marine] WITH NOCHECK ADD
CONSTRAINT [PK_marine] PRIMARY KEY  CLUSTERED
(
  [claimid]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[gpa] WITH NOCHECK ADD
CONSTRAINT [PK_gpa] PRIMARY KEY  CLUSTERED
(
  [claimid]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Claim] WITH NOCHECK ADD
CONSTRAINT [DF_Claim_updated] DEFAULT (getdate()) FOR [updated],
CONSTRAINT [DF_Claim_usrname] DEFAULT (suser_sname()) FOR [usrname]
GO

CREATE  INDEX [IDX_Claim2] ON [dbo].[Claim]([cpkey]) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[Rand_DFT]', N'[Claim].[firstest]'
GO

EXEC sp_bindefault N'[dbo].[Rand_DFT]', N'[Claim].[Total]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[Rand_DFT]', N'[marine].[Insval]'
GO

setuser
GO

ALTER TABLE [dbo].[Claim] ADD
CONSTRAINT [FK_Claim_Assessor] FOREIGN KEY
(
  [Assid]
) REFERENCES [dbo].[Assessor] (
  [AssessorID]
),
CONSTRAINT [FK_Claim_Cause] FOREIGN KEY
(
  [cause]
) REFERENCES [dbo].[Cause] (
  [CauseID]
),
CONSTRAINT [FK_Claim_claimheader] FOREIGN KEY
(
  [headerid]
) REFERENCES [dbo].[claimheader] (
  [headerid]
),
CONSTRAINT [FK_Claim_CP_Covertype] FOREIGN KEY
(
  [cpkey]
) REFERENCES [dbo].[CP_Covertype] (
  [CPCKey]
),
CONSTRAINT [FK_Claim_Fault] FOREIGN KEY
(
  [fault]
) REFERENCES [dbo].[Fault] (
  [FaultID]
),
CONSTRAINT [FK_Claim_Peril] FOREIGN KEY
(
  [peril]
) REFERENCES [dbo].[Peril] (
  [PerilID]
),
CONSTRAINT [FK_Claim_PolicySection] FOREIGN KEY
(
  [polsection]
) REFERENCES [dbo].[PolicySection] (
  [PSKey]
),
CONSTRAINT [FK_Claim_Resc] FOREIGN KEY
(
  [resc]
) REFERENCES [dbo].[Resc] (
  [RescID]
),
CONSTRAINT [FK_Claim_status] FOREIGN KEY
(
  [Status]
) REFERENCES [dbo].[status] (
  [StatCode]
)
GO

ALTER TABLE [dbo].[Motor] ADD
CONSTRAINT [FK_Motor_Claim] FOREIGN KEY
(
  [claimid]
) REFERENCES [dbo].[Claim] (
  [ClaimID]
) ON DELETE CASCADE  ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[assets] ADD
CONSTRAINT [FK_assets_Claim] FOREIGN KEY
(
  [claimid]
) REFERENCES [dbo].[Claim] (
  [ClaimID]
) ON DELETE CASCADE  ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[marine] ADD
CONSTRAINT [FK_marine_Claim] FOREIGN KEY
(
  [claimid]
) REFERENCES [dbo].[Claim] (
  [ClaimID]
) ON DELETE CASCADE  ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[gpa] ADD
CONSTRAINT [FK_gpa_Claim] FOREIGN KEY
(
  [claimid]
) REFERENCES [dbo].[Claim] (
  [ClaimID]
) ON DELETE CASCADE  ON UPDATE CASCADE
GO

Robert Bravery wrote:
Show quoteHide quoteTurn the joins into outer joins. That way you can include each case in
the same query.

....
FROM dbo.Claim
LEFT JOIN dbo.Motor
   ON dbo.Claim.ClaimID = dbo.Motor.claimid
LEFT JOIN dbo.assets
   ON dbo.Claim.ClaimID = dbo.assets.claimid
... etc

Help w/sum pls
transaction rollback : preferred technique
Bulk Insert Related Tables - PKs
Best Practices for Design of Views
Changes to order by in sql 2005
CSV From SELECT query
<<< I couldnt backup my SQL Databases >>> !!!!!!!!!!!!!!!!
Jobs steps order all wrong
Blocking processes
Dynamic Date/Calendar