Inner Join Query incorrect result

Ashok Karale

12y1.7k

Hi I am developing an application in VS 2010, C# and SQLCe database.

I am generating a report from 3 tables respectively Income, Expence and Transactions.

table structure is as below:

Income:

Hi I am developing an application in VS 2010, C# and SQLCe database.

I am generating a report from 3 tables respectively Income, Expence and Transactions.

table structure is as below:

Income:

Income

Expence:

expence

Transactions:

trans

For this I wrote query

select t.tDate as [Date], t.tDescription as [Detail],e.eAmount as [Debit],

i.iAmount as [Credit], t.balance as [Balance] from Transactions t 

inner join Expence e on t.pid=e.pid join Income i on t.pid=i.pid where t.pid='11'


But when I run this query I am just getting

result

I want that my result should be like a bank statement as below.

final

As per my understanding query is not right.

How can we write query to get result like this?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashok Karale12y ago
Jignesh I want query result like
Jignesh Trivedi12y ago
hi,
Question is not too much clear
can you please share output that you want?
pradeep kumar12y ago
This Is just example for your Understanding purpose::

CREATE TABLE #INCOME
(
IncomeId INT IDENTITY(1,1),
InCome MONEY
)

CREATE TABLE #Expense
(
ExpenseId INT IDENTITY(1,1),
IncomeId INT,
Expense MONEY
)

INSERT INTO #INCOME(InCome)
VALUES (10000)

DECLARE @IncomeId INT

SET @IncomeId= IDENT_CURRENT('#INCOME')

INSERT INTO #Expense(IncomeId,Expense)
VALUES (@IncomeId,2000)

SELECT
EX.Expense,INC.IncomeId
FROM #Expense EX
INNER JOIN #INCOME INC ON INC.IncomeId=EX.IncomeId

DROP TABLE #Expense
DROP TABLE #INCOME