How to join the results of two subqueries in PostgreSQL?

Question

hello i'm newbie in sql (postgresql)
i have 2 tables as result of 2 differen selects

       all calls                    our customer contacts
   number contact_id      and     contact_id    name
    3213      12                        12     jonh
    3213      34                        16     michael
    3213      43                        65     hewlet
    5432      16                        32     steward
    5432      51
    6543      65
    2322      54
    2322      32

1 number can belong to different contacts... (contacts belong to different customers) i need to select distinct numbers from 1st result table. and names of this contacts from 2nd table..

and how i must unite my 2 selects

thanks.

Answer 1

You will not be able to use the distinct keyword, as you actually want to select also the contact_id from the all_calls table. Instead, you will need to use one of the aggregate functions to select a single contact_id for each distinct phone number.

In this example I use the min() function, which will give me the contact with the numerically lowest contact_id for each phone number:

select tmp.number, contacts.name
from (
  select number, min(contact_id) as min_id
  from all_calls
  group by number
) as tmp
join contacts on tmp.min_id = contacts.contact_id