Post reply

Please help in Outer join

  • April 20, 2006 at 2:02 am

    #190273

    Hi All

    The below query is (Old STYLE) working fine and also gived the desired result

    but the second query gives only the matching records only(like inner Join)

    Need help to solve this problem

    1. select h.salesrep_Name ,disp_code,description,count(act_status)

    from  modifiedcalldata m ,histdata h, dispositions d

    where m.prog_sol_number =* h.prog_sol_number and

    m.act_status =* d.disp_code  and month(calldate) = 3 

    group by h.salesrep_Name ,disp_code,description --WITH ROLLUP

    order by h.salesrep_Name ,disp_code,description

    GO

     

    2. select h.salesrep_Name ,disp_code,description,count(act_status)

    from  modifiedcalldata m

    right outer join histdata h      on m.prog_sol_number = h.prog_sol_number

    right outer join dispositions d  on  d.disp_code = m.act_status

    where month(calldate) = 3 

    group by h.salesrep_Name ,disp_code,description --WITH ROLLUP

    order by h.salesrep_Name ,disp_code,description

    GO

     

  • April 20, 2006 at 2:09 am

    #633182

    Which table column "calldate" belongs to?

    Which table is main in this query?

    _____________
    Code for TallyGenerator

  • April 20, 2006 at 2:54 am

    #633191

    CallDate belongs to Modifiedcalldata

  • April 20, 2006 at 3:30 am

    #633198

    So,

    select h.salesrep_Name ,disp_code,description,count(act_status)

    from modifiedcalldata m

    LEFT outer join histdata h on m.prog_sol_number = h.prog_sol_number

    LEFT outer join dispositions d on d.disp_code = m.act_status

    where month(calldate) = 3

    group by h.salesrep_Name ,disp_code,description

    order by h.salesrep_Name ,disp_code,description

    must work

    _____________
    Code for TallyGenerator

  • April 20, 2006 at 3:34 am

    #633200

    hi, the following article might help :-

    http://www.sqlservercentral.com/columnists/sjones/outerjointrouble.asp

    paul

  • April 20, 2006 at 4:10 am

    #633203

    My guess would be

    SELECT h.salesrep_Name,d.disp_code,[description],count(m.act_status)

    FROM histdata h

    CROSS JOIN dispositions d

    LEFT OUTER JOIN modifiedcalldata m

      ON m.prog_sol_number = h.prog_sol_number

      AND m.act_status = d.disp_code

      AND month(m.calldate) = 3

    GROUP BY h.salesrep_Name, d.disp_code, [description] --WITH ROLLUP

    ORDER BY h.salesrep_Name, d.disp_code, [description]

    but really need ddl and an explanation of what is required

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 20, 2006 at 4:21 am

    #633208

    Thanks lot. Its working fine.. David Burrows 

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply