CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
C Sharper
C Sharper

Reputation: 8626

How do I check if a table have matching record in another table?

I have following tables:

Table 1 : AccessMenu

userid  item

1        a 

2        b    

2        c

and

Table 2 : ActionRights

idAdmin   itemCol

2          v

2          m

2          d

DESIRED RESULT:

  userid      yes/no

   1           no

   2           yes

When I get matching userid to idadmin in ActionRight it should return Yes otherwise No.

I tried below query:

select AccessMenu.userid,
case when ActionRights.IdAdmin=AccessMenu.userid then 'Yes' else 'No' end as 'GRP'
from AccessMenu left join ActionRights
on ActionRights.IdAdmin =AccessMenu.userid

But through this query i am getting repeated result as:

 userid      yes/no

   1           no

   2           yes

   2           yes

   2           yes

   2           yes

   2           yes

Please help me.

Upvotes: 0

Views: 79

Answers (4)

Ajay Kumar
Ajay Kumar

Reputation: 175

Try this

SELECT distinct  UserId, CASE WHEN  IsAdmin  IS  NULL  THEN  'No'  ELSE  'Yes' END AS 'Grp'
FROM (  
      select AccessMenu.userid as UserId ,ActionRights.IsAdmin as IsAdmin
      from AccessMenu left join ActionRights
      on ActionRights.IsAdmin =AccessMenu.userid
     )a

Have a look

http://www.sqlfiddle.com/#!2/07d52/10

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT userid, 
       CASE 
         WHEN idadmin IS NULL THEN 'No' 
         ELSE 'Yes' 
       END AS 'Grp' 
FROM   (SELECT accessmenu.userid    AS UserId, 
               actionrights.idadmin AS IdAdmin 
        FROM   accessmenu 
               LEFT JOIN actionrights 
                      ON actionrights.idadmin = accessmenu.userid)z

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

Its simple. No need of Inner queries or Subqueries. Just take the Distinct

 SELECT DISTINCT AM.USERID,
 CASE WHEN AR.USERID IS NULL THEN 'NO' ELSE 'YES' END AS [YES/NO] 
 FROM #ACCESSMENU AM
 LEFT JOIN #ACCESSRIGHT AR ON AM.USERID=AR.USERID

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please check using subquery:

select distinct
    userid, 
    case when (SELECT COUNT(*) from ActionRights b where b.idAdmin=a.userid)>0 then 'Yes' else 'No' end [Yes/No]
from AccessMenu a

Upvotes: 1

Related Questions