create table TopHeadConfTbl (
PartNumber varchar(14) primary key not null,
TpHdTypeID varchar(4) not null)


create table BotHeadConfTbl (
PartNumber varchar(14) primary key not null,
BotHdTypeID varchar(4) not null)

create table QuoDetTbl(
MainAsID varchar(14)not null,
PartNumber varchar(14) not null references (TopHeadConfTbl,BotHeadConfTbl) ,
ItemNumber varchar(14) notnull)

quote:

---

Originally posted by RickD

OK can't be completed like you are asking. Will you always have a QuoDetTbl record? If so, swap you references and say they have to exist in that table to be able to exist in the others.

If you think what you have is right, then you will need to do this in your middle level logic (stored procs, triggers etc) instead as it seems you have to have the PartNumber existing in Top or Bot tables.

---

create table TopHeadConfTbl (
PartNumber varchar(14) primary key not null,
TpHdTypeID varchar(4) not null)


create table BotHeadConfTbl (
PartNumber varchar(14) primary key not null,
BotHdTypeID varchar(4) not null)

create table QuoDetTbl(
MainAsID varchar(14)not null,
PartNumber varchar(14) not null ,
ItemNumber varchar(14) not null)

insert into TopHeadConfTbl (PartNumber ,TpHdTypeID ) values ('TH001','001'),
															('TH002','002')

insert into BotHeadConfTbl (PartNumber ,BotHdTypeID ) values ('BH001','003'),
															 ('BH002','004')

insert into QuoDetTbl (MainAsID ,PartNumber ,ItemNumber ) values	('M001','TH001','1.1'),
																	('M001','BH002','1.2')
																	
select MainAsID ,coalesce (TopHeadConfTbl .PartNumber, BotHeadConfTbl.PartNumber) as PartNumber, coalesce (TopHeadConfTbl .TpHdTypeID, BotHeadConfTbl.BotHdTypeID) as HeadType,ItemNumber 
from QuoDetTbl
left join TopHeadConfTbl on TopHeadConfTbl .PartNumber = QuoDetTbl .PartNumber 
left join BotHeadConfTbl on BotHeadConfTbl .PartNumber = QuoDetTbl .PartNumber  

quote:

---

Originally posted by udaaf

I have problem for create relation between table. I have one table refer to 2 table with same field. Could someone help me how to solve this problem. Here's the code.

create table TopHeadConfTbl (
PartNumber varchar(14) primary key not null,
TpHdTypeID varchar(4) not null)


create table BotHeadConfTbl (
PartNumber varchar(14) primary key not null,
BotHdTypeID varchar(4) not null)

create table QuoDetTbl(
MainAsID varchar(14)not null,
PartNumber varchar(14) not null references (TopHeadConfTbl,BotHeadConfTbl) ,
ItemNumber varchar(14) notnull)

Thanks,

Afri

---