How to join the results of a subquery to a table 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.

Solution

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

Comments

[Falcon]

thanks. but why min() ?. and "our customer contacts" its result of select id from contacts where customer_id = '51'

[Jørn Schou-Rode]

As I understand your question, you just want one name pr. called phone number. If two contacts have been called on the same number, a min() or max() on the contact_id can be used to select just one value. Assuming that contact_id does not hold any business value, both functions are in practice "random".

[Jørn Schou-Rode]

Wouldn't you be able to simply append where customer_id = 51 to the end of the query? Of course, this assumes that no two contacts from different companies share the same phone number.