Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: LEFT JOIN Help Please

    Hi all,

    I need some help with a left join and I don't even know if this is possible so any guidance would be of help.

    I have two tables, Appointments, and Appointee_Costing. Both are joined by EMP_NO, and CONTRACT_NO

    What i want to do is take the record in the appointment table, and join it to the appointee_costing table in such a way that I get the related records from the appointee_costing table. To begin with I've used this;

    Code:
    select		a.emp_no, a.contract_no, b.cost_effective_date, b.cost_code
    from		appointments a
    left join	appointee_costing b
    on		a.emp_no = b.emp_no
    and		a.contract_no = b.contract_no
    This works, However there may be dozens of entries in the appointee_table for the appointment but I only want the current one. I then modified the join to work only on the maximum effective_date in the appointee_costing table...

    Code:
    select		a.emp_no, a.contract_no, b.cost_effective_date, b.cost_code 
    from		appointments a
    left join	appointee_costing b
    on		a.emp_no = b.emp_no
    and		a.contract_no = b.contract_no
    where		a.cost_effective_date = 
    	(
    		select	max(bb.cost_effective_date)
    		from	appointee_costing bb
    		where	bb.emp_no = b.emp_no
    		and	bb.contract_no = b.contract_no
    	)
    Now the problem. This gave me what I wanted for everyone where there was a costing entry, but what I need, is to display NULL where there is no associated costing record and that's the bit where I'm having trouble.

    What I am getting is this...

    EMP_NO, CONTRACT_NO, COST_EFFECTIVE_DATE, COST_CODE
    210000, 1, 2014-04-01, AAAABA
    202000, 1, 2014-01-01, AAABAA
    200300, 3, 2014-02-01, AABAAA
    200005, 1, 2014-06-01, BAAAAA

    What I want, is this...

    EMP_NO, CONTRACT_NO, COST_EFFECTIVE_DATE, COST_CODE
    210000, 1, 2014-04-01, AAAABA
    202000, 1, 2014-01-01, AAABAA
    200300, 3, 2014-02-01, AABAAA
    200040, 1, NULL, NULL
    200005, 1, 2014-06-01, BAAAAA

    Is this possible? Can someone offer some suggestions?

    Christy
    Last edited by christyxo; 06-18-14 at 12:58. Reason: tidying...

Posting Permissions

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