Joining the two tables problem.

Question

Table1

ID Date

001 01/02/2009
001 02/02/2009
...
...
001 28/02/2009
002 01/02/2009
002 02/02/2009
...
...
002 28/02/2009

Table2

ID Date Salary

001 02/02/2009 800
001 25/02/2009 500
002 01/02/2009 300
...,

I want to join the two tables

Tried Query

SELECT table1.id, table1.date, table2.salary 
FROM table1 
LEFT OUTER JOIN table2 ON
table1.id = table2.id AND table1.date = table2.date

Result

Id Date Salary

001 02/02/2009 800
001 25/02/2009 500
002 01/02/2009 300

I want to display a result like all the id, date from table1 and salary from table2 where table1.date = table2.date

Expected Output

ID Date Salary

001 01/02/2009 
001 02/02/2009 800
001 03/02/2009 
...
...
001 25/02/2009 500
...
001 28/02/2009 
002 01/02/2009 300
002 02/02/2009 
...
...
002 28/02/2009 
...

How to make a query in SQL

Need Query Help

Answer 1

I think you need right outer join.

Answer 2

As a test, I've used the data and query you've provided giving following result.

Note that salary 500 is not returned because there's no date match in the data you've provided.

Result

id          date                    salary
----------- ----------------------- -----------
1           2009-02-01 00:00:00.000 NULL
1           2009-02-02 00:00:00.000 800
1           2009-02-28 00:00:00.000 NULL
2           2009-02-01 00:00:00.000 300
2           2009-02-02 00:00:00.000 NULL
2           2009-02-28 00:00:00.000 NULL

Script

DECLARE @Table1 TABLE (ID INTEGER, Date DATETIME)
DECLARE @Table2 TABLE (ID INTEGER, Date DATETIME, Salary INTEGER)

INSERT INTO @Table1 VALUES (001, '02/01/2009')
INSERT INTO @Table1 VALUES (001, '02/02/2009')
INSERT INTO @Table1 VALUES (001, '02/28/2009')
INSERT INTO @Table1 VALUES (002, '02/01/2009')
INSERT INTO @Table1 VALUES (002, '02/02/2009')
INSERT INTO @Table1 VALUES (002, '02/28/2009')

INSERT INTO @Table2 VALUES (001, '02/02/2009', 800)
INSERT INTO @Table2 VALUES (001, '02/25/2009', 500)
INSERT INTO @Table2 VALUES (002, '02/01/2009', 300)


Select  table1.id, table1.date, table2.salary 
from    @table1 table1
        left outer join @table2 table2 on table1.id = table2.id and table1.date = table2.date