Multiple records same id retrieval

Question

I have a table with the following structure

USERID	CONTACT	VALUE
1	LANDLINE	+12312322334
1	MOBILE	+11234567890
1	EMAIL	[email protected]
2	LANDLINE	+12312322335
2	MOBILE	+11234567899
3	LANDLINE	+12312322336
3	MOBILE	+11234567898

I want to have a result set of all users that have an email contact and list the result set as

USERID	MOBILE	EMAIL
1	+11234567890	[email protected]

Tried looking it up but can't remember the term of the needed query for thus, which made it hard for me find the solution.

Can anyone help and suggest the most efficient query for this scenario? This is for oracle db by the way

Answer 1

In your case, what you need is a self-join:

SELECT
    contact_email.USERID
    ,contact_email.VALUE AS EMAIL
    ,contact_mobile.VALUE AS MOBILE
FROM
    contacts AS contact_email
LEFT OUTER JOIN
    contacts AS contact_mobile
    ON contact_mobile.USERID = contact_email.USERID
    AND contact_mobile.CONTACT = 'MOBILE'
WHERE
    contact_email.contact = 'EMAIL'
;

The choice of a LEFT OUTER JOIN means that you will also get contacts for which no matching mobile phone record can be found. If you want to omit such contacts, use an INNER JOIN instead.

Answer 2

Another option might be conditional aggregation (without it, result would be displayed in several rows) along with EXISTS which filters out users who don't have EMAIL as a contact option.

Sample data:

SQL> with test (userid, contact, value) as
  2    (select 1, 'LANDLINE', '+12312322334'   from dual union all
  3     select 1, 'MOBILE'  , '+11234567890'   from dual union all
  4     select 1, 'EMAIL'   , '[email protected]' from dual union all
  5     select 2, 'LANDLINE', '+12312322335'  from dual union all
  6     select 2, 'MOBILE'  , '+11234567899'  from dual union all
  7     select 3, 'LANDLINE', '+12312322336'  from dual union all
  8     select 3, 'MOBILE'  , '+11234567898'  from dual
  9    )

Query:

 10  select a.userid,
 11    max(case when a.contact = 'MOBILE' then a.value end) as mobile,
 12    max(case when a.contact = 'EMAIL'  then a.value end) as email
 13  from test a
 14  where exists (select null
 15                from test b
 16                where b.userid = a.userid
 17                  and b.contact = 'EMAIL'
 18               )
 19  group by a.userid;

    USERID MOBILE         EMAIL
---------- -------------- --------------
         1 +11234567890   [email protected]

SQL>