quote:

---

Originally posted by sazd1

Infact my intention to combine two tables is that I have two tables
DailyTransaction(IncomeTable) and DailyExpenses(ExpenditureTable). I want to display income and expenditure in a single table and at the end of table i will calculate the NetIncome or NetLoss.
so I just want to display incometable columns and expendituretable columns in a single table to create a report for Netincome or Netloss.
Thanks.

---

CREATE TABLE IncomeTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
JOIN1 VARCHAR(10)
)

CREATE TABLE ExpenditureTable
(
ID INT  REFERENCES JOIN1(ID),
JOIN2  VARCHAR(10)
)

INSERT INTO IncomeTable
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F' 


INSERT INTO ExpenditureTable
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 4,'D' 


SELECT *
FROM	IncomeTable A
		INNER JOIN
		ExpenditureTable B
ON A.ID	=	B.ID  -- Like this


--DROP TABLE IncomeTable
--DROP TABLE ExpenditureTable

So you need to join both the tables using common field.

SELECT A.Column1,A.Column2,A.Column3,B.Column4,B.Column5,B.Column6
FROM	IncomeTable A
		INNER JOIN
		ExpenditureTable B
ON A.ItemId 	=	B.ItemId  

Dim cmdText As String = "Select EDescription, SUM(EQuantity) AS EQuantity, " & _
        "SUM(EAmount) AS EAmount, TDescription,SUM(TQuantity) AS TQuantity, SUM(TAmount) AS TAmount " & _
        "From " & _
        "(" & _
        "SELECT de.Description AS EDescription, de.Quantity AS EQuantity, de.Amount AS EAmount," & _
        "0 AS TDescription, 0 AS TQuantity,0 AS TAmount FROM DailyExpenses de " & _
        "WHERE de.EDate Between @START and @END " & _
        "UNION ALL " & _
        "SELECT 0 AS EDescription, 0 AS EQuantity, 0 AS EAmount, " & _
        "dt.Description AS TDescription,dt.Quantity AS TQuantity,dt.Amount AS TAmount FROM DailyTransaction dt " & _
        "WHERE dt.DTDate Between @START and @END " & _
        ") a " & _
        "GROUP BY EDescription,TDescription"