开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜