Join Query

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

Empid Date Pts(DIv1) Pts(DIv2) Pts(DIv3)
V001 07-24-2004 Null Null 25
V002 07-24-2004 20 Null 25
V003 07-24-2004 Null 30 NUll
V001 07-23-2004 15 Null NUll
V002 07-23-2004 10 25 25
V001 07-22-2004 Null 10 25

I'm badly in need of help. Any sort of help is appreciated.

M.L.Srinivas<code>
SELECT T1.EmpId,T1.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM Table1 as T1
INNER JOIN Table2 as T2 on T1.EmpId=T2.EmpId and T1.Date=T2.Date
INNER JOIN Table3 as T3 on T2.EmpId=T3.EmpId and T2.Date=T3.Date
WHERE <i>condition</i>
</code>|||Hi,

Thanks for your reply..as you used inner join it works only for dates which are available in table1. What if the person scored only in second or third div. on a day and not in first div.

Please see my data sample carefully..i beleive we have to use full outer join...i was succesful for two tables..but how about three tables...

here is my query

select 'Tdate'= case
when a.date is null then b.date
else a.date
end,
a.points,b.points from table1 as a full outer join table2 as b
on a.empid=b.empid and a.date=b.date

Please guide me

M.L.Srinivas|||<code>
SELECT T.EmpId,T.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM
(SELECT EmpId,Date FROM Table1
UNION
SELECT EmpId,Date FROM Table2
UNION
SELECT EmpId,Date FROM Table3
) as T
LEFT JOIN Table1 as T1 ON T.EmpId=T1.EmpId and T.Date=T1.Date
LEFT JOIN Table2 as T2 ON T.EmpId=T2.EmpId and T.Date=T2.Date
LEFT JOIN Table3 as T3 ON T.EmpId=T3.EmpId and T.Date=T3.Date
WHERE condition
</code>