Subquery - need to add column for match and include all data

1

In this query i can get the event_name which occurs with both types of worker role. But I want to have all of the rows showing and then some kind of tic column to show that the ones that match both conditions, ie in both types of worker roles.
select wv1.worker_role, wv1.event_name from event_worker_role_view wv1 where wv1.worker_role in('A31 Service Provider')

and wv1.event_name in (select wv2.event_name from event_worker_role_view wv2 where
wv2.worker_role = 'A29i Licensed Clinician')

2

Have you tried a "Join" statement uniting two queries? ...possibly in the following format:

(selectQuery1) WHERE variable IN (selectQuery2)

and add a checkbox for each record, with Checkbox.Checked = True (if the condition is met)?

3

How would you make the join? this did not work-Incorrect syntax near the keyword 'inner'.

select wv1.worker_role, wv1.event_name from event_worker_role_view wv1 where wv1.worker_role in('A31 Service Provider')

and wv1.event_name in (select wv2.event_name from event_worker_role_view wv2 where
wv2.worker_role = 'A29i Licensed Clinician')
inner join event_worker_role_view wv2
on wv1.worker_role = wv2.worker_role

4

Try this

select 
     wv1.worker_role
   , wv1.event_name 
from 
    ( select  * from  event_worker_role_view  where worker_role = 'A29i Licensed Clinician'	) wv2
	   inner join 
    ( select * from event_worker_role_view where  worker_role = 'A31 Service Provider' ) wv1
	      on 
		     wv1.worker_role = wv2.worker_role 
			    and 
  			 wv1.event_name = wv2.event_name
5

hi this did not yield any results but I will check further looks like the way.

6

As mentioned before, you will get better answers if you take the time to provide consumable test data.
Also, it would probably be more efficient to work with the tables and not a view.
At a guess:

WITH Both
AS
(
	SELECT event_name
	FROM dbo.event_worker_role_view
	WHERE worker_role IN ('A31 Service Provider', 'A29i Licensed Clinician')
	GROUP BY event_name
	HAVING COUNT(DISTINCT worker_role) = 2
)
SELECT WR.worker_role, WR.event_name
	,CASE
		WHEN B.event_name IS NOT NULL
		THEN 'Y'
		ELSE 'N'
	END AS Both
FROM dbo.event_worker_role_view WR
	LEFT JOIN Both B
		ON WR.event_name = B.event_name;