Limit the data set of a single table within a multi-table sql select statement

I'm working in an Oracle environment.

In a 1:M table relationship I want to write a query that will bring me each row from the "1" table and only 1 matching row from the "many" table.

To give a made up example... ( * = Primary Key/Foreign Key )

EMPLOYEE
*emp_id
name
department

PHONE_NUMBER
*emp_id
num

There are many phone numbers for one employee.

Let's say I wanted to return all employees and only one of their phone numbers. (Please forgive the far-fetched example. I'm trying to simulate a workplace scenario)

I tried to run:

SELECT emp.*, phone.num
FROM EMPLOYEE emp
JOIN PHONE_NUMBER phone
    ON emp.emp_id = phone.emp_id
WHERE phone.ROWNUM <= 1;

It turns out (and it makes sense to me now) that ROWNUM only exists within the context of the results returned from the entire query. There is not a "ROWNUM" for each table's data set.

I also tried:

SELECT emp.*, phone.num
FROM EMPLOYEE emp
JOIN PHONE_NUMBER phone
    ON emp.emp_id = phone.emp_id
WHERE phone.num = (SELECT MAX(num)
                   FROM PHONE_NUMBER);

That one just returned me one row total. I wanted the inner SELECT to run once for each row in EMPLOYEE.

I'm not sure how else to think about this. I basically want my result set to be the number of rows in the EMPLOYEE table and for each row the first matching row in the PHONE_NUMBER table.

Obviously there are all sorts of ways to do this with procedures and scripts and such but I feel like there is a single-query solution in there somewhere...

Any ideas?

Solution

If you want only one phone number, then use row_number():

SELECT e.*, p.num
FROM EMPLOYEE emp JOIN
     (SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) as seqnum
      FROM PHONE_NUMBER p
     ) p
    ON e.emp_id = p.emp_id and seqnum = 1;

Alternatively, you can use aggregation, to get the minimum or maximum value.