Could also be written as a CTE.
Moving Gold and Silver to the partners table, or other changes to more normalize (keys instead of descriptions) will change things.
select
[partner_id]
,[competency_name]
,[program]
,[SKUgroup]
,[competencyname]
,[start_date]
,[end_date]
from [dbo].[partners] a
inner join [dbo].[mastercompetency] b on a.[competency_name]=b.[competencyname]
where
[partner_id] IN (
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] IN ('Gold Cloud Productivity','Gold Small and MidMarket Cloud Solutions')
group by
[partner_id]
HAVING count([competency_name]) = 2
UNION
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] = 'Silver Small and MidMarket Cloud Solutions')