Join Query Help

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

this is what you get when you don't know how to design databases, this guy has the same problem: http://forums.aspfree.com/t33633/s.html
i suggest you also look at those tutorials

what needs to be done for your specific case:

make 1 table with the points in and field to distinguis between divisions, instead of putting them in 3 tables:
PointsTable: empid,date,points, divisionid
DivisionTable: divisionid, name

then you have the right database structure and everything becomes easy:
your result:
SELECT empid, date, sum(points)
FROM pointstable
GROUP BY empid, date

Hi Kris_Vanherck,

Thanks for your time. Let me make it clear, actually I don't have three different tables. The
structure of my table (some/imp columns)
ActivityDate
Task
Div1Id
Div1pts
DivId
Div2Pts
Div3Id
Div3Pts
One person performs the task and it will be reviewed by one or two persons.
For example, if V001 performs a task, his empid(V001) will be Div1id and points scored by him
will be div1pts. if he reviews some others tasks, his empid will be under div2id or div3id
depending the reviewed time.
So, on any given date i want the sum of points accumulated seperately in all three divisions by
all employees.
Sample data follows:
ActDate Task Div1Id Div1Pts Div2Id Div2pts Div3id Div3Pts

07-24-2004 T1 V001 20 V002 15 NULL NUll
07-24-2004 T2 V003 20 V001 15 V004 10
07-24-2004 T3 V001 20 V004 15 NULL NUll
07-23-2004 T4 V004 20 V001 15 V002 15
07-23-2004 T5 V001 20 V004 15 NULL NUll

And the output should be like:
Empid Date Points(DIv1) POints(DIv2) POints(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

As I could not accomplish this task in one query, I adopted a round about process like , creating
three views (the three tables I mentioned in earlier post) and trying to write a query using full
outer join.
Any sort of help is appreciated.
Thanks
M.L.Srinivas

if i get this strait it means you want for each date a the list of empid's like this:
date, empid, sum(of all div1pts with date = date and div1id = empid), sum(of all div2pts with date = date and div1id = empid), sum(of all div3pts with date = date and div1id = empid)

don't have much time rigth now so here a quick stab at it:

create view:
SELECT date, div1id as empid, sum(div1pts) as p1,0 as p2, 0 as p3 ... group by date, div1id
UNION
SELECT date, div2id as empid, 0 as p1,sum(div2pts) as p2, 0 as p3 ... group by date, div2id
UNION
SELECT date, div3id as empid, 0 as p1,0 as p2, sum(div3pts) as p3 ... group by date, div3id

then

select date, empid, sum(p1),sum(p2),sum(p3) from view group by data empid

Hi Kris_Vanherck,

Brilliant. Thanks alot. I appreciate your logic.

I have got my output.

Thanks

M.L.Srinivas