Skip to main content
AI Assist is now on Stack Overflow. Start a chat to get instant answers from across the network. Sign up to save and share your chats.

Return to Answer

added 100 characters in body
Source Link
Stu
Stu
  • 32.7k
  • 6
  • 17
  • 34

One other way is a correlated query using exists which should be very performant:

select * from #Product p where exists ( select * from #Product p2 where p2.ProductNumber = p.ProductNumber group by p2.productNumber having Min(p2.productType) = 1 and Max(p2.productType) = 5 );

Demo DB<>Fiddle

One other way is a correlated query using exists which should be very performant:

select * from #Product p where exists ( select * from #Product p2 where p2.ProductNumber = p.ProductNumber group by p2.productNumber having Min(p2.productType) = 1 and Max(p2.productType) = 5 );

One other way is a correlated query using exists which should be performant:

select * from #Product p where exists ( select * from #Product p2 where p2.ProductNumber = p.ProductNumber group by p2.productNumber having Min(p2.productType) = 1 and Max(p2.productType) = 5 );

Demo DB<>Fiddle

Source Link
Stu
Stu
  • 32.7k
  • 6
  • 17
  • 34

One other way is a correlated query using exists which should be very performant:

select * from #Product p where exists ( select * from #Product p2 where p2.ProductNumber = p.ProductNumber group by p2.productNumber having Min(p2.productType) = 1 and Max(p2.productType) = 5 );