CODEWITHSUNDEEP
mysql
Karthikeyan
Karthikeyan

Reputation: 93

nested select query more efficient

Am having user & user_code table having one to many relationship. Please find the below table structure.

User table

 id email   username    date
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
679 [email protected]   sathis  4/20/2019 9:04
680 [email protected]   ram 4/20/2019 9:04
680 [email protected]   ram 4/20/2019 9:04
680 [email protected]   ram 4/20/2019 9:04
681 [email protected]   Steve   4/20/2019 9:04
681 [email protected]   Steve   4/20/2019 9:04
681 [email protected]   Steve   4/20/2019 9:04
681 [email protected]   Steve   4/20/2019 9:04
681 [email protected]   Steve   4/20/2019 9:04

User_Code table

user_id code
679 J039
679 J080
679 J320
679 J54L
679 K31P
679 L05C
679 T030
679 V150
680 J039
680 J080
680 J320
681 ABC12
681 CD123
681 opo123
681 qw123
681 ieu12

if i give the username (from user table) then it should check for the code (from user_code table) and the same code should check whether it is presented for some other user_id(from user_code table) and should return that user_ids also.

for example.

  id    username
679 sathis
680 ram

from the above table, if i get sathis as input. i should get ram as output results. Because Sathis's code also presented for Ram also.

Please find my sql nested query below 

select id,username from users
where 
id in (
select user_id from user_code
where
code in (
select code from user_code
where user_id = ( select id from user where username = 'sathis')))

How can i simplify this query more efficient,

Upvotes: 1

Views: 55

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

for improve performance you could avoid IN clause and use inner join on subquery 

select id, username 
from users 
inner join (
  select distinct user_id 
  from User_Code 
  inner join  (
    select code 
    from User_Code 
    inner join User ON User_Code.user_id = user.id
    where  username = 'sathis'
  ) t on t.code = User_Code.code
) t2 on t2.user_id = users.id

Upvotes: 1

Related Questions