• thanks,

    i try to provide create table and insert value statements

    tblDocuments:

    USE [EDMS]

    GO

    /****** Object: Table [dbo].[tblDocuments] Script Date: 06/25/2013 23:44:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    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

    tbltransmittalNo

    USE [EDMS]

    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

    tblTransmittalls

    USE [EDMS]

    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

    tblTranstoCon

    USE [EDMS]

    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

    USE [EDMS]

    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 [EDMS].[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 [EDMS].[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 [EDMS].[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 [EDMS].[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 [EDMS].[dbo].[tblDocTranstoCon]

    ([DocID]

    ,[TransId]

    ,[TRANSMITtoConId])

    VALUES

    (1,1,1),

    (1,1,3),

    (2,1,2)

    GO

    Query:

    SELECT dbo.tblDocuments.DocumentNo, dbo.tbltransmittalNo.TRANSMITTAL, dbo.tblTranstoCon.TRANSMITtoCon

    FROM dbo.tblDocuments INNER JOIN

    dbo.tblTransmittalls ON dbo.tblDocuments.DocId = dbo.tblTransmittalls.DocID INNER JOIN

    dbo.tbltransmittalNo ON dbo.tblTransmittalls.TransId = dbo.tbltransmittalNo.TransID INNER JOIN

    dbo.tblDocTranstoCon ON dbo.tblTransmittalls.DocID = dbo.tblDocTranstoCon.DocID AND

    dbo.tblTransmittalls.TransId = dbo.tblDocTranstoCon.TransId INNER JOIN

    dbo.tblTranstoCon ON dbo.tblDocTranstoCon.TRANSMITtoConId = dbo.tblTranstoCon.TRANSMITtoConId

    -------------------------------------------------------------

    This query returns below data:

    DocNoTransmittalNoTransmittaltoCon

    Doc-001tt-0002con-0003

    Doc-001tt-0002con-0007

    Doc-005tt-0002con-0005

    I like to make a query that returns also below data:

    IDPARENTPOSITION OPTION

    100 DOC-0001

    210 TT-0002

    301 DOC-0005

    421 CON-0007

    530 TT-0002

    65 0 CON-0003