Querying two tables with columns named the same

I have two tables in a SQL Server database: table1 and table2

t开发者_高级运维able1:

 DATE        NAME    CREDIT
 1/1/2011    BALU      100 
 1/3/2011    BALU      200 

table2:

 DATE        NAME    DEBIT
 1/2/2011    BALU      100 
 1/3/2011    BALU       50 

I want to display the report in following format:

 DATE        NAME    CREDIT  DEBIT
 1/1/2011    BALU      100      0
 1/2/2011    BALU        0    100
 1/3/2011    BALU      200     50

My problem is that both tables have a date column.

How can I write the SELECT statement with a WHERE clause given that both tables have the same column named date?

I am still not positive about what you want, several details are missing. But based on experience working in similar queries I think this is what you might need (or close enough).

SELECT ISNULL(C.DATE, D.DATE) DATE, ISNULL(C.NAME, D.NAME) NAME, SUM(C.CREDIT) CREDIT, SUM(D.DEBIT) DEBIT
FROM TABLE_1 C
FULL JOIN TABLE2 D ON D.DATE=C.DATE AND D.NAME=C.NAME
WHERE ISNULL(C.DATE, D.DATE) BETWEEN @DATE1 AND @DATE2
GROUP BY C.DATE, D.DATE, C.NAME, D.NAME

If I understand your question correctly, thi is what you want:

SELECT  ISNULL(T1.Date,T2.Date) Date, ISNULL(T1.Name,T2.Name) Name, 
        ISNULL(T1.Credit,0) Credit, ISNULL(T2.Debit,0) Debit
FROM table1 T1
FULL JOIN table2 T2
ON T1.Date = T2.Date AND T1.Name = T2.Name

SELECT COALESCE(t1.[DATE], t2.[DATE]) [DATE], 
    COALESCE(t1.[NAME], t2.[NAME]) [NAME], 
    COALESCE(t1.[CREDIT], 0) [CREDIT], 
    COALESCE(t2.[DEBIT], 0) [DEBIT]
  FROM Table1 t1
  FULL JOIN Table2 t2 
    ON (t1.[DATE] = t2.[DATE]) AND (t1.[NAME] = t2.[NAME])

I guess you are looking for the datediff function in sql. http://msdn.microsoft.com/fr-fr/library/ms189794.aspx Still i might be wrong since your question is not clearly stated.

Since you don't specify which date is first in the between and the FK linking both table, i will assume..

 select ta.date, ta.name, ta.credit, tb.debit from table1 ta
 join table2 tb as (ta.name = tb.name)
 where ta.date between tb.date