Find out which report assigned to particular role

Here is common Question is. How to find which report assing to particular role :

Here we have two tables
First one :reportmaster
Create table reportmaster(
Reportid int, reportname varchar(50)
)

Insert into reportmaster(reportname)
Select 1,'sanjay test report'
Union
Select 2, 'sanjay test report1'
Union
Select 3, 'sanjay test report2'

Second table : reportdetail

Create table reportdetail(
Id int,
Reportid int,
Roleid int)

Insert into reportdetail(reportid,roleid)
Select 1,25
Union
Select 2,25

Now question is : which report name like test assigned to role 25.

Solution is :

Select * from reportdetail a
Inner join reportmaster b on a.reportid=b.reportid
Where roleid=25 and reportid in
(Select reportid from reportmaster where reportname like '%test%')

Tag : sql, sql query, sql tables