Query to list users having same profile from two tables

Question

Table 1 : dbt_m_user (user_id, user_name, email_id, . . . . . )

Table 2 : dbt_m_user_profile (user_id, profile_id, . . . . .)

I have e-mail id of an user who has particular profile (role). Now I need names of users who are having same access profile.

I could get the results from below, but it looks very raw and doesn't seem to be elegant one, considering that there are only 2 tables.

  select user_name from dbt_m_user where user_id in (
  select user_id from dbt_m_user_profile where profile_id in (
  select profile_id from dbt_m_user_profile where user_id in ( 
  select user_id from  dbt_m_user where Email_Id = 'snehal.masne@db.com' )))

Can this be improvised by using special clauses/joins ?

Add some sample table data and the expected result. (As formatted text, not images.) — jarlh, Dec 05 '17 at 13:25
Are you using MySQL or Oracle? Don't tag products not involved. — jarlh, Dec 05 '17 at 13:26
If the user_id is the value you're looking for, you can omit the outer select; it doesn't add anything. — Ronald, Dec 05 '17 at 13:27

Harshil Doshi · Answer 1 · 2017-12-05T13:41:48.110

Following query should work:

select t1.user_name
from dbt_m_user_profile t1
inner join dbt_m_user t2
on t2.user_id = t1.user_id
where t2.profile_id in (select t1.profile_id
                       from dbt_m_user_profile t1
                       inner join (select * from dbt_m_user 
                                   where t1.email_id = 'snehal.masne@db.com'
                                  ) t2
                       on t2.user_id = t1.user_id                           
                       )
;

Ramesh · Answer 2 · 2017-12-05T13:51:23.107

0

How about this one? I haven't tested it though

   SELECT u.username
     FROM dbt_m_user u 
     JOIN dbt_m_user_profile p
      ON  (u.user_id = p.user_id)
     JOIN dbt_m_user_profile up
      ON  (p.profile_id = up.profile_id)
    JOIN  dbt_m_user ou
      ON  (up.user_id = ou.user_id)
   WHERE ou.email_id = 'snehal.masne@db.com';

edited Dec 05 '17 at 13:51

answered Dec 05 '17 at 13:35

Ramesh

1,405
10
19

@SnehalMasne - Didnt notice that. How about the changed one? – Ramesh Dec 05 '17 at 13:51

score 0 · Answer 3 · answered Dec 05 '17 at 14:09

0

Try below query

SELECT    U.USER_NAME 
FROM      DBT_M_USER_PROFILE P, DBT_M_USER U 
WHERE     P.USER_ID = U.USER_ID 
AND       P.PROFILE_ID IN  (
              SELECT P.PROFILE_ID
              FROM  DBT_M_USER_PROFILE P, DBT_M_USER U
              WHERE U.EMAIL_ID = 'snehal.masne@db.com'
              AND  P.USER_ID = U.USER_ID  )

answered Dec 05 '17 at 14:09

Vidya Pandey

211
2
10

The query should work as expected but it's always better to use ANSI Syntax for `JOIN`. For more info :https://stackoverflow.com/a/1018825/2225030 – Harshil Doshi Dec 05 '17 at 15:36

Query to list users having same profile from two tables

3 Answers3