I am going mad with this Query. I need to join 3 Tables. Their Formats
are

Vouchers
        [VoucherID] [uniqueidentifier] NOT NULL ,
    [VoucherTypeID] [int] NOT NULL ,
    [VoucherNo] [int] NULL ,
    [VoucherDate] [datetime] NOT NULL ,
    [VoucherNarration] [varchar] (255)
    CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
    (
        [VoucherID]
    )  ON [PRIMARY]


Ledgers
    [LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
    [LedgerName] [varchar] (50) COLLATE
    CONSTRAINT [PK_Ledgers] PRIMARY KEY  CLUSTERED
    (
        [LedgerID]
    )  ON [PRIMARY]


CREATE TABLE [Transactions] (
    [TransactionID] [uniqueidentifier] NOT NULL ,
    [VoucherID] [uniqueidentifier] NOT NULL ,
    [ByTo] [char] (1)
    [LedgerID] [int] NOT NULL ,
    [Credit] [money] NOT NULL ,
    [Debit] [money] NOT NULL ,
    CONSTRAINT [PK_Transactions] PRIMARY KEY  CLUSTERED
    (
        [TransactionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
    (
        [LedgerID]
    ) REFERENCES [Ledgers] (
        [LedgerID]
    ),
    CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
    (
        [VoucherID]
    ) REFERENCES [Vouchers] (
        [VoucherID]
    )
) ON [PRIMARY]
GO


The Required Output is

ID VoucherNo VoucherDate LedgerName  Amount               
1  1     2001-09-03      Bank-1      2400.00
2  2     2001-09-03      Cash        600.00
3  3     2001-09-03      TAX A/C     0.00
4  4     2001-09-03      Bank-1      4000.00
5  5     2001-09-03      Bank-1
0.00

But, I am getting More than One row from the transactions table. I just
need the first matching row

ID VoucherNo VoucherDate LedgerName  Amount               
1  1     2001-09-03      Bank-1      2400.00
2  2     2001-09-03      Cash        600.00
3  3     2001-09-03      TAX A/C     0.00
4  4     2001-09-03      Bank-1      4000.00
5  4     2001-09-03      Cash        400.00
6  5     2001-09-03      Bank-1      0.00
7  5     2001-09-03      Cash        5035.00

The Query I am using is

SELECT    dbo.Vouchers2001.VoucherID,    
        dbo.Vouchers2001.VoucherNo,
    dbo.Vouchers2001.VoucherDate, 
        dbo.Ledgers.LedgerName,
        SUM(dbo.Transactions2001.Debit) AS Amount

FROM    dbo.Vouchers2001 INNER JOIN
        dbo.Transactions2001
        ON dbo.Vouchers2001.VoucherID =
          dbo.Transactions2001.VoucherID INNER JOIN
          dbo.Ledgers ON dbo.Transactions2001.LedgerID = 
                                dbo.Ledgers.LedgerID
WHERE   (dbo.Vouchers2001.VoucherTypeID = 1)

GROUP BY dbo.Vouchers2001.VoucherID,
         dbo.Ledgers.LedgerName,
    dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo,
         dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
         dbo.Ledgers.LedgerName,
         dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo

Plz help Out

*** Sent via Developersdex http://www.developersdex.com ***

Can you post the sample data that gives the (incorrect) output.

If I remove the Ledgers Table from the Join the query works perfectly
but I need the Name of the Ledger.

SELECT    dbo.Vouchers2001.VoucherID,    
        dbo.Vouchers2001.VoucherNo,
    dbo.Vouchers2001.VoucherDate
        SUM(dbo.Transactions2001.Debit) AS Amount

FROM    dbo.Vouchers2001 INNER JOIN
        dbo.Transactions2001
        ON dbo.Vouchers2001.VoucherID =
          dbo.Transactions2001.VoucherID

WHERE   (dbo.Vouchers2001.VoucherTypeID = 1)

GROUP BY dbo.Vouchers2001.VoucherID
     dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo,
         dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
         dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo

Voucher Table

7332429B-22BF-49B8-A5B6-00094FF9DD59 4             1
2001-07-04 19:54:00.000                               
26D65B5D-E389-4E74-8605-000EC9F10575 14            1
2001-07-30 15:49:00.000                               
A40996E6-0581-48D9-91A9-000F1E0D0B46 14            1
2001-09-17 15:23:00.000                               
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4             1
2001-09-15 21:48:00.000                               
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4             1
2001-06-04 18:28:00.000       

Transactions Table
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B    1           .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T    25          48.0000
.0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B    1           .0000
.5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T    1           2.0000
.0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B    232         .0000
.5000


Ledger Table

1           Cash            
2           Profit & Loss A/c
3           Branch-1        
4           ReserveSurplus-1
5           DutiesTaxes-1   


This is some sample data not related to the output I submitted int the
previous post.

*** Sent via Developersdex http://www.developersdex.com ***

I'm having some difficulty following your sample
data. However, it looks like a single voucher
can have multiple transactions and each transaction
can have its own ledger. Therefore one voucher
can have multiple ledgers, hence your problem.

So in the scenario where you have voucher X
with transactions T1 and T2 and transaction T1
has ledger L1 and transaction T2 has ledger L2
it means that voucher X has two possible ledgers.
For your query which ledger do you want for a voucher?
If you just want any one of them you can do this

SELECT  dbo.Vouchers2001.VoucherID,
        dbo.Vouchers2001.VoucherNo,
        dbo.Vouchers2001.VoucherDate,
        MIN(dbo.Ledgers.LedgerName),
        SUM(dbo.Transactions2001.Debit) AS Amount


FROM    dbo.Vouchers2001 INNER JOIN
        dbo.Transactions2001
        ON dbo.Vouchers2001.VoucherID =
          dbo.Transactions2001.VoucherID INNER JOIN
          dbo.Ledgers ON dbo.Transactions2001.LedgerID =
                                dbo.Ledgers.LedgerID
WHERE   (dbo.Vouchers2001.VoucherTypeID = 1)


GROUP BY dbo.Vouchers2001.VoucherID,
         dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo,
         dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
         dbo.Vouchers2001.VoucherDate,
         dbo.Vouchers2001.VoucherNo

Thanks, Your code works.

What I need is the First row in the transactions which match. That is
the first row which was saved to the table during the data-entry



*** Sent via Developersdex http://www.developersdex.com ***

Bill Bob (nospam@devdex.com) writes:
As I have pointed out to Bill Bob in comp.databases.ms-sqlserver this
information appears not to be available in his tables.

Please do not post the same question independently to multiple newsgroups.
This is very impolite, as this can cause people to spend time on problems
that already have been solved.

Timeout expired - on a simple SqlDataReader:Read() call
Primary Key Question
Is it possible to join two tables in a View/Query where the Second Table is based on a Name in the F
OBJECTPROPERTY problem
T-SQL: How to loop through the resultset from another procedure?