SELECT
 vi.VendorItemID,
 ItemID,
   vi.VendorID,
      VendorDescription,
      VendorName,
      VendorCode,
      uom.UomDescription As PurchaseUnit,
      IntroductionDate,
      DiscontinueDate,
      AnyRuleApplied = CASE WHEN   zonerule.ZoneCount > 0 OR entityrule.EntityCount > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END
 FROM
       tbVendorItem  vi  with (nolock)
       inner join tbVendor v with (nolock) on vi.VendorID = v.VendorID
       inner join  tbzone z with (nolock) on v.VendorZoneId = z.ZoneID
       inner join tbZoneType zt with (nolock) on z.ZoneTypeId = zt.ZoneTypeId
       inner join tbUnitOfMeasure uom with (nolock) on vi.PurchaseUnitID = uom.UnitOfMeasureID
       left join (Select VendorItemID, Count(*) As ZoneCount from dbo.tbVendorItemZoneRule Group By VendorItemID) As ZoneRule on vi.VendorItemID = zonerule.VendorItemID
       left join (Select VendorItemID, Count(*) As EntityCount from dbo.tbVendorItemEntityRule Group By VendorItemID) As EntityRule on vi.VendorItemID = entityrule.VendorItemID
  where zt.ZoneType = 'Location - DistributorName'
  order by VendorDescription