PL/SQL - How to return single row from a joined table

Question

this might be quite simple I'm just not seeing the wood for the trees at the moment. In Oracle I'm selecting records from table A that joins to table B based on the primary key of table A. However table B can have multiple records matching the primary key of table A. This is causing my query to return duplicate rows from table A. Below is a cut down version of my query:

TableA                TableB
_______               _________
1, Sec1                2, 11/01/2011
2, Sec2                2
3, Sec3                5, 10/01/2011
4, Sec4                6, 10/01/2011

Select A.SecID, A.SecName, B.DateSent from tableA A   
  inner join tableB B on A.SecID = B.SecID

This is returning 2 records for Sec2 - how can I get it to return only 1 record for Sec2? I've tried using distinct and unique but still get the same results.

Answer 1

SELECT  secid, secname
FROM    tableA
WHERE   secid IN
        (
        SELECT  secid
        FROM    tableb
        )

If you need a record from tableB as well:

SELECT  secid, secname, datesent
FROM    (
        SELECT  a.secid, a.secname, b.datesent, ROW_NUMBER() OVER (PARTITION BY a.secid ORDER BY b.datesent DESC) AS rn
        FROM   tableA a
        JOIN   tableB b
        ON     b.secid = a.secid
        )
WHERE   rn = 1

ORDER BY clause controls which of the multiple records on b will you get.

Answer 2

You can use a GROUP function to select only one row:

SELECT A.SecID, A.SecName, max(B.DateSent) DateSent
  FROM tableA A   
  JOIN tableB B on A.SecID = B.SecID
 GROUP BY A.SecID, A.SecName