|
|
Posted by Erland Sommarskog on 02/19/06 00:59
Bill Bob (nospam@devdex.com) writes:
> I am going mad with this Query. I need to join 3 Tables. Their Formats
> are
>...
> 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
I'm afraid that if you want help, you will need to put more effort
into your postings.
It appears that your output produce rows that are not desired, but
you don't give any business rules to select the "first matching row".
What I see is that you have a voucher table, but the same voucher number
can appear more than once. This looks very strange to me, but I don't
know the business domain. Vochers are apparently linked to transactions
by voucher id, and since you use SUM, I assumed that there can be more
than one transction per voucher id.
Anyway, for this type of postings it is recommendable, to post:
1) CREATE TABLE statements for the tables.
2) INSERT statements with sample data.
3) The desired output given the sample data.
4) A short narrative, describing the business problem.
1 and 2 makes it easy to copy and paste into a query tool to develop
a query, that can be verified against 3.
In a separate post, you posted some sample data, but they are not
related to the desired output above, and furthermore, I cannot see
that there are any matching voucher ids.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|