Join two distinct select resultsets
I have two select statements that look like this
SELECT DISTINCT(NAME, PHONE_NUMBER) FROM PHONE_CALLS GROUP BY NAME, PHONE_NUMBER;
SELECT DISTINCT(NAME, PHONE_NUMBER) FROM MISSED_CALLS GROUP BY NAME, PHONE_NUMBER;
These are two separate tables.
I would like to join the result sets of each of these statements on the PHONE_CALLS phone_number so the result would look like
开发者_运维技巧phone_calls.NAME phone_calls.PHONE_NUMBER missed_calls.NAME, missed_calls.PHONE_NUMBER
...if there was a match.. Is this possible? Thanks,
select distinct pc.name, pc.phone_number, mc.name missed_name
from phone_calls pc, missed_calls mc
where pc.phone_number = mc.phone_number
This will give you all phone numbers having both received and missed calls. If you also need those that have only received or missed calls, use
select distinct
pc.name,
coalesce(pc.phone_number,mc.phone_number) phone_number,
mc.name missed_name
from phone_calls pc
full outer join missed_calls mc on pc.phone_number = mc.phone_number
instead.
精彩评论