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

Question

It can be pretty simple. I just don't see a tree for trees at the moment. In Oracle, I select records from table A that join table B based on the primary key of table A. However, table B may have several records matching the primary key of table A. This prompts my query to return duplicate rows from table A. Below is an abbreviated version of my request:

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 returns 2 records for Sec2 - how can I make it return only 1 record for Sec2? I tried using different and unique ones, but still get the same results.

Answer 1

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

If you need an entry from tableB :

 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 determines which of the several entries on b you get.

Answer 2

You can use the 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 

Answer 3

 SELECT DISTINCT a.secid, a.secname FROM tableA a, tableB b WHERE a.secid = b.secid; 

Answer 4

The proposed solutions are very good. There are times when you may have a slightly different approach, especially when one of the tables is very large compared to the other and the lack of an index in the foreign key column in table B.