No your sample data does not work as posted. Here is the script after removing all the references to EDMS.
CREATE TABLE [dbo].[tblDocuments](
[Originator] [nvarchar](255) NOT NULL,
[Document Type] [nvarchar](255) NOT NULL,
[Unit] [nvarchar](255) NOT NULL,
[EQ-IN Identify/Material Code] [nvarchar](255) NOT NULL,
[Sequential No] [nvarchar](50) NULL,
[DocId] [int] IDENTITY(17314,1) NOT NULL,
[DocumentNo] [nvarchar](200) NOT NULL,
[TITLE] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_tblDocuments_1] PRIMARY KEY CLUSTERED
(
[DocId] 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
/****** Object: Table [dbo].[tbltransmittalNo] Script Date: 06/25/2013 23:56:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbltransmittalNo](
[TRANSMITTAL] [nvarchar](200) NOT NULL,
[TransID] [int] IDENTITY(8858,1) NOT NULL,
[RevDATE] [datetime] NULL,
[Originator Code] [nvarchar](50) NULL,
[Originator Location] [nvarchar](50) NULL,
[Recipient Code] [nvarchar](50) NULL,
[Recipient Location] [nvarchar](50) NULL,
[Serial No] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[Reciever] [nvarchar](50) NULL,
[Attention] [nvarchar](50) NULL,
CONSTRAINT [PK_tbltransmittalNo_1] PRIMARY KEY CLUSTERED
(
[TransID] 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
/****** Object: Table [dbo].[tblTransmittalls] Script Date: 06/25/2013 23:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTransmittalls](
[DocID] [int] NOT NULL,
[REV] [nvarchar](255) NULL,
[TransId] [int] NOT NULL,
[Pages] [nvarchar](50) NULL,
[Class] [nvarchar](50) NULL,
[Size] [nvarchar](50) NULL,
[PurposeofIssue] [nvarchar](50) NULL,
[DefaultForDocument] [bit] NULL,
CONSTRAINT [PK_tblTransmittalls_1] PRIMARY KEY CLUSTERED
(
[DocID] ASC,
[TransId] 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
ALTER TABLE [dbo].[tblTransmittalls] WITH CHECK ADD CONSTRAINT [FK_tblTransmittalls_tblDocuments] FOREIGN KEY([DocID])
REFERENCES [dbo].[tblDocuments] ([DocId])
GO
ALTER TABLE [dbo].[tblTransmittalls] CHECK CONSTRAINT [FK_tblTransmittalls_tblDocuments]
GO
ALTER TABLE [dbo].[tblTransmittalls] WITH NOCHECK ADD CONSTRAINT [FK_tblTransmittalls_tbltransmittalNo] FOREIGN KEY([TransId])
REFERENCES [dbo].[tbltransmittalNo] ([TransID])
GO
ALTER TABLE [dbo].[tblTransmittalls] NOCHECK CONSTRAINT [FK_tblTransmittalls_tbltransmittalNo]
GO
ALTER TABLE [dbo].[tblTransmittalls] ADD CONSTRAINT [DF_tblTransmittalls_DefaultForDocument] DEFAULT ((0)) FOR [DefaultForDocument]
GO
/****** Object: Table [dbo].[tblTranstoCon] Script Date: 06/25/2013 23:59:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTranstoCon](
[TRANSMITtoConId] [int] IDENTITY(2212,1) NOT NULL,
[TRANSMITtoCon] [nvarchar](50) NOT NULL,
[Oldtransmittocon] [nvarchar](255) NULL,
[RevDATE] [datetime] NULL,
[Originator] [nvarchar](255) NULL,
[old Recipient] [nvarchar](255) NULL,
[Recipient] [nvarchar](255) NULL,
[Serial No] [nvarchar](255) NULL,
[Subject] [nvarchar](255) NULL,
[Type Code] [nvarchar](255) NULL,
[Copies] [nvarchar](255) NULL,
[Remark] [nvarchar](255) NULL,
CONSTRAINT [PK_tblTranstoCon_1] PRIMARY KEY CLUSTERED
(
[TRANSMITtoConId] 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
/****** Object: Table [dbo].[tblDocTranstoCon] Script Date: 06/26/2013 00:00:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDocTranstoCon](
[DocID] [int] NOT NULL,
[TransId] [int] NOT NULL,
[TRANSMITtoConId] [int] NOT NULL,
CONSTRAINT [PK_tblDocTranstoCon] PRIMARY KEY CLUSTERED
(
[DocID] ASC,
[TransId] ASC,
[TRANSMITtoConId] 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
ALTER TABLE [dbo].[tblDocTranstoCon] WITH CHECK ADD CONSTRAINT [FK_tblDocTranstoCon_tblTransmittalls] FOREIGN KEY([DocID], [TransId])
REFERENCES [dbo].[tblTransmittalls] ([DocID], [TransId])
GO
ALTER TABLE [dbo].[tblDocTranstoCon] CHECK CONSTRAINT [FK_tblDocTranstoCon_tblTransmittalls]
GO
ALTER TABLE [dbo].[tblDocTranstoCon] WITH CHECK ADD CONSTRAINT [FK_tblDocTranstoCon_tblTranstoCon] FOREIGN KEY([TRANSMITtoConId])
REFERENCES [dbo].[tblTranstoCon] ([TRANSMITtoConId])
GO
ALTER TABLE [dbo].[tblDocTranstoCon] CHECK CONSTRAINT [FK_tblDocTranstoCon_tblTranstoCon]
GO
---------------------------------------------------------------
INSERT INTO [dbo].[tblDocuments]
([Originator]
,[Document Type]
,[Unit]
,[EQ-IN Identify/Material Code]
,[Sequential No]
,[DocumentNo]
,[TITLE])
VALUES
('Abad','dw','100','1441','001','doc-001','test1'),
('paya','dw','140','1441','005','doc-005','test2')
GO
INSERT INTO [dbo].[tbltransmittalNo]
([TRANSMITTAL]
,[RevDATE]
,[Originator Code]
,[Originator Location]
,[Recipient Code]
,[Recipient Location]
,[Serial No]
,[Subject]
,[Originator]
,[Reciever]
,[Attention])
VALUES
('tt-0002', null,null,null,null,null,null,null,null,null,null)
GO
-------------------------------------------------------------------
INSERT INTO [dbo].[tblTransmittalls]
([DocID]
,[REV]
,[TransId]
,[Pages]
,[Class]
,[Size]
,[PurposeofIssue]
,[DefaultForDocument])
VALUES
(1,'0',1,null,null,null,null,null),
(2,'0',1, null,null,null,null,null)
GO
------------------------------------------------------------
INSERT INTO [dbo].[tblTranstoCon]
([TRANSMITtoCon]
,[Oldtransmittocon]
,[RevDATE]
,[Originator]
,[old Recipient]
,[Recipient]
,[Serial No]
,[Subject]
,[Type Code]
,[Copies]
,[Remark])
VALUES
('con-0003',null,null,null,null,null,null,null,null,null,null),
('con-0005',null,null,null,null,null,null,null,null,null,null),
('con-0007',null,null,null,null,null,null,null,null,null,null)
GO
INSERT INTO [dbo].[tblDocTranstoCon]
([DocID]
,[TransId]
,[TRANSMITtoConId])
VALUES
(1,1,1),
(1,1,3),
(2,1,2)
GO
Here are the results:
(2 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblTransmittalls_tblDocuments". The conflict occurred in database "test", table "dbo.tblDocuments", column 'DocId'.
The statement has been terminated.
(3 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblDocTranstoCon_tblTransmittalls". The conflict occurred in database "test", table "dbo.tblTransmittalls".
The statement has been terminated.
If I remove the foreign key constraints on all those tables like this:
CREATE TABLE [dbo].[tblDocuments](
[Originator] [nvarchar](255) NOT NULL,
[Document Type] [nvarchar](255) NOT NULL,
[Unit] [nvarchar](255) NOT NULL,
[EQ-IN Identify/Material Code] [nvarchar](255) NOT NULL,
[Sequential No] [nvarchar](50) NULL,
[DocId] [int] IDENTITY(17314,1) NOT NULL,
[DocumentNo] [nvarchar](200) NOT NULL,
[TITLE] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_tblDocuments_1] PRIMARY KEY CLUSTERED
(
[DocId] 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
/****** Object: Table [dbo].[tbltransmittalNo] Script Date: 06/25/2013 23:56:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbltransmittalNo](
[TRANSMITTAL] [nvarchar](200) NOT NULL,
[TransID] [int] IDENTITY(8858,1) NOT NULL,
[RevDATE] [datetime] NULL,
[Originator Code] [nvarchar](50) NULL,
[Originator Location] [nvarchar](50) NULL,
[Recipient Code] [nvarchar](50) NULL,
[Recipient Location] [nvarchar](50) NULL,
[Serial No] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[Reciever] [nvarchar](50) NULL,
[Attention] [nvarchar](50) NULL,
CONSTRAINT [PK_tbltransmittalNo_1] PRIMARY KEY CLUSTERED
(
[TransID] 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
/****** Object: Table [dbo].[tblTransmittalls] Script Date: 06/25/2013 23:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTransmittalls](
[DocID] [int] NOT NULL,
[REV] [nvarchar](255) NULL,
[TransId] [int] NOT NULL,
[Pages] [nvarchar](50) NULL,
[Class] [nvarchar](50) NULL,
[Size] [nvarchar](50) NULL,
[PurposeofIssue] [nvarchar](50) NULL,
[DefaultForDocument] [bit] NULL,
CONSTRAINT [PK_tblTransmittalls_1] PRIMARY KEY CLUSTERED
(
[DocID] ASC,
[TransId] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTranstoCon](
[TRANSMITtoConId] [int] IDENTITY(2212,1) NOT NULL,
[TRANSMITtoCon] [nvarchar](50) NOT NULL,
[Oldtransmittocon] [nvarchar](255) NULL,
[RevDATE] [datetime] NULL,
[Originator] [nvarchar](255) NULL,
[old Recipient] [nvarchar](255) NULL,
[Recipient] [nvarchar](255) NULL,
[Serial No] [nvarchar](255) NULL,
[Subject] [nvarchar](255) NULL,
[Type Code] [nvarchar](255) NULL,
[Copies] [nvarchar](255) NULL,
[Remark] [nvarchar](255) NULL,
CONSTRAINT [PK_tblTranstoCon_1] PRIMARY KEY CLUSTERED
(
[TRANSMITtoConId] 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
/****** Object: Table [dbo].[tblDocTranstoCon] Script Date: 06/26/2013 00:00:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDocTranstoCon](
[DocID] [int] NOT NULL,
[TransId] [int] NOT NULL,
[TRANSMITtoConId] [int] NOT NULL,
CONSTRAINT [PK_tblDocTranstoCon] PRIMARY KEY CLUSTERED
(
[DocID] ASC,
[TransId] ASC,
[TRANSMITtoConId] 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
---------------------------------------------------------------
INSERT INTO [dbo].[tblDocuments]
([Originator]
,[Document Type]
,[Unit]
,[EQ-IN Identify/Material Code]
,[Sequential No]
,[DocumentNo]
,[TITLE])
VALUES
('Abad','dw','100','1441','001','doc-001','test1'),
('paya','dw','140','1441','005','doc-005','test2')
GO
INSERT INTO [dbo].[tbltransmittalNo]
([TRANSMITTAL]
,[RevDATE]
,[Originator Code]
,[Originator Location]
,[Recipient Code]
,[Recipient Location]
,[Serial No]
,[Subject]
,[Originator]
,[Reciever]
,[Attention])
VALUES
('tt-0002', null,null,null,null,null,null,null,null,null,null)
GO
-------------------------------------------------------------------
INSERT INTO [dbo].[tblTransmittalls]
([DocID]
,[REV]
,[TransId]
,[Pages]
,[Class]
,[Size]
,[PurposeofIssue]
,[DefaultForDocument])
VALUES
(1,'0',1,null,null,null,null,null),
(2,'0',1, null,null,null,null,null)
GO
------------------------------------------------------------
INSERT INTO [dbo].[tblTranstoCon]
([TRANSMITtoCon]
,[Oldtransmittocon]
,[RevDATE]
,[Originator]
,[old Recipient]
,[Recipient]
,[Serial No]
,[Subject]
,[Type Code]
,[Copies]
,[Remark])
VALUES
('con-0003',null,null,null,null,null,null,null,null,null,null),
('con-0005',null,null,null,null,null,null,null,null,null,null),
('con-0007',null,null,null,null,null,null,null,null,null,null)
GO
INSERT INTO [dbo].[tblDocTranstoCon]
([DocID]
,[TransId]
,[TRANSMITtoConId])
VALUES
(1,1,1),
(1,1,3),
(2,1,2)
GO
Now all the objects and data are created. But your query returns an empty result set. If I change ALL of the joins to left joins it returns only 2 rows.
SELECT dbo.tblDocuments.DocumentNo, dbo.tbltransmittalNo.TRANSMITTAL, dbo.tblTranstoCon.TRANSMITtoCon
FROM dbo.tblDocuments
left JOIN dbo.tblTransmittalls ON dbo.tblDocuments.DocId = dbo.tblTransmittalls.DocID
left JOIN dbo.tbltransmittalNo ON dbo.tblTransmittalls.TransId = dbo.tbltransmittalNo.TransID
left JOIN dbo.tblDocTranstoCon ON dbo.tblTransmittalls.DocID = dbo.tblDocTranstoCon.DocID AND
dbo.tblTransmittalls.TransId = dbo.tblDocTranstoCon.TransId
left JOIN dbo.tblTranstoCon ON dbo.tblDocTranstoCon.TRANSMITtoConId = dbo.tblTranstoCon.TRANSMITtoConId
I am willing to help you but we have to have a common ground to get started. For the sake of our discussion the foreign keys can probably be left off but I have to be able to produce the same results as you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/