Getting first line of a LEFT OUTER JOIN

Question

I have 3 tables:

(SELECT DISTINCT ID
FROM IDS)a
LEFT OUTER JOIN
(SELECT NAME, ID
FROM NAMES)b
ON a.ID = b.ID
LEFT OUTER JOIN
(SELECT ADDRESS FROM ADDRESSES
WHERE ROWNUM <2
ORDER BY UPDATED_DATE DESC)c
ON a.ID = c.ID

An ID can have only one name but can have multiple addresses. I only want the latest one. This query returns the address as null even when there is an address I guess cause it only fetches the first address from the table and then tries LEFT JOIN it to the ID of addresses which it canno find. What is the correct way of writing this query?

Solution 1

Try KEEP DENSE_RANK

Data source:

CREATE TABLE person
    (person_id int primary key, firstname varchar2(4), lastname varchar2(9))
/
INSERT ALL
    INTO person (person_id, firstname, lastname)
         VALUES (1, 'john', 'lennon')
    INTO person (person_id, firstname, lastname)
         VALUES (2, 'paul', 'mccartney')
SELECT * FROM dual;



CREATE TABLE address
    (person_id int, address_id int primary key, city varchar2(8))
/
INSERT ALL
    INTO address (person_id, address_id, city)
         VALUES (1, 1, 'new york')
    INTO address (person_id, address_id, city)
         VALUES (1, 2, 'england')
    INTO address (person_id, address_id, city)
         VALUES (1, 3, 'japan')
    INTO address (person_id, address_id, city)
         VALUES (2, 4, 'london')
SELECT * FROM dual;

Query:

    select  

      p.person_id, p.firstname, p.lastname,

      x.recent_city

    from person p
    left join (

        select person_id,      

            min(city) -- can change this to max(city). will work regardless of min/max

            -- important you do this to get the recent: keep(dense_rank last)

            keep(dense_rank last order by address_id) 
               as recent_city

        from address 
        group by person_id


    ) x on x.person_id = p.person_id

Live test: http://www.sqlfiddle.com/#!4/7b1c9/2

---

Not all database has similar functionality with Oracle's KEEP DENSE_RANK windowing function, you can use plain windowing function instead:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city, x.pick_one_only

from person p
left join (

    select 

        person_id,      

        row_number() over(partition by person_id order by address_id desc) as pick_one_only,
        city as recent_city

    from address 



) x on x.person_id = p.person_id and x.pick_one_only = 1

Live test: http://www.sqlfiddle.com/#!4/7b1c9/48

---

Or use tuple testing, shall work on databases that doesn't support windowing function:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city

from person p
left join (

    select   
        person_id,city as recent_city    
    from address 
    where (person_id,address_id) in

          (select person_id, max(address_id)
           from address
           group by person_id)



) x on x.person_id = p.person_id 

Live test: http://www.sqlfiddle.com/#!4/7b1c9/21

---

Not all database supports tuple testing like in the preceding code though. You can use JOIN instead:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city

from person p
left join (

    select 

        address.person_id,address.city as recent_city

    from address 
    join 
    (
          select person_id, max(address_id) as recent_id
           from address
           group by person_id
    ) r 
    ON address.person_id = r.person_id
    AND address.address_id = r.recent_id



) x on x.person_id = p.person_id 

Live test: http://www.sqlfiddle.com/#!4/7b1c9/24

Solution 2

You can use the analytic function RANK

(SELECT DISTINCT ID
   FROM IDS) a
LEFT OUTER JOIN
(SELECT NAME, ID
   FROM NAMES) b
ON a.ID = b.ID
LEFT OUTER JOIN
(SELECT ADDRESS ,
        rank() over (partition by id
                         order by updated_date desc) rnk
   FROM ADDRESSES) c
ON (    a.ID = c.ID 
    and c.rnk = 1)

Solution 3

(SELECT DISTINCT ID
FROM IDS)a
LEFT OUTER JOIN
(SELECT NAME, ID
FROM NAMES)b
ON a.ID = b.ID
LEFT OUTER JOIN
(SELECT ADDRESS, ROWNUMBER() OVER(PARTITON BY ID ORDER BY UPDATED_DATE DESC) RN
 FROM ADDRESSES 
)c
ON a.ID = c.ID
where c.RN=1

Solution 4

Without having access to any database at the moment, you should be able to do

(SELECT DISTINCT ID 
    FROM IDS) a LEFT OUTER JOIN
(SELECT NAME, ID 
    FROM NAMES)b ON a.ID = b.ID LEFT OUTER JOIN
(SELECT TOP 1 ADDRESS 
    FROM ADDRESSES 
    ORDER BY UPDATED_DATE DESC) c ON a.ID = c.ID

As you might see, the "TOP 1" at 'Address' will only return the first row of the result set. Also, are you sure that a.ID and c.ID is the same?
I would imagine you need something like .... c ON a.ID = c.AddressID If not, i'm not entirely sure how you link multiple addresses to a single ID.

View more solutions

Comments

[Eosphorus]

well the address table has no unique primary key sadly and one ID can have multiple IDS

[Justin Cave]

TOP 1 isn't valid Oracle syntax

[Neville]

ah, didn't notice the oracle keyword

[Srisudhir T]

Excellent solution, was looking all around and found this perfect