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')