Results 1 to 4 of 4

Thread: Join Query

  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: 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

  2. #2
    Join Date
    Mar 2004
    Posts
    80
    <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>

  3. #3
    Join Date
    Jun 2004
    Posts
    5

    Unhappy

    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

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    <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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •