|
|
Posted by Tom Moreau on 02/18/06 15:03
The first column in the resultset is ID, but you don't say from which the ID
originates, since there is no such column in any table. Also, you did not
provide INSERT statements of sample data.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Bill Bob" <nospam@devdex.com> wrote in message
news:MTzJf.91$6M5.14383@news.uswest.net...
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 ***
Navigation:
[Reply to this message]
|