select "nsmes"."id" as "nsme_id", "nsmes"."logo" as "nsme_logo", "nsmes"."en_name" as "nsme_en_name", "nsmes"."ar_name" as "nsme_ar_name", "opportunities"."id", "opportunities"."ref_number" as "ref_key", "opportunities"."title", "opportunities"."status", "published_date", "locations"."en_location", "locations"."ar_location", "opportunities"."due_date", "opportunities"."description", "categories"."name" as "category", COUNT(sme_branches.sme_id) as qualified from "opportunities" inner join "locations" on "opportunities"."delivery_location_id" = "locations"."id" inner join "nsmes" on "nsmes"."id" = "opportunities"."nsme_id" inner join "categories" on "categories"."id" = "opportunities"."category_id" left join "opportunity_bidding_locations" on "opportunity_bidding_locations"."opportunity_id" = "opportunities"."id" left join "sme_branches" on "sme_branches"."location_id" = "opportunity_bidding_locations"."location_id" and "sme_branches"."capital" >= "opportunities"."min_capital" and ("sme_branches"."capital" <= "opportunities"."max_capital" and "opportunities"."max_capital" is not null or "sme_branches"."capital" >= 0 and "opportunities"."max_capital" is null) and "sme_branches"."established_year" <= "opportunities"."to_estb_yr" and "sme_branches"."established_year" >= "opportunities"."from_estb_yr" and "sme_branches"."sme_id" = 'c643d1b4-49b6-11e6-beb8-9e71128cae77' inner join "sme_categories" on "sme_categories"."category_id" = "opportunities"."category_id" left join "bids" as "B1" on "B1"."opportunity_id" = "opportunities"."id" left join "sme_branches" as "SB1" on "SB1"."id" = "B1"."sme_branch_id" and "SB1"."sme_id" = 'c643d1b4-49b6-11e6-beb8-9e71128cae77' where "sme_categories"."sme_id" = 'c643d1b4-49b6-11e6-beb8-9e71128cae77' group by "opportunities"."id", "nsmes"."id", "locations"."id", "categories"."id" having COUNT("SB1".sme_id) = 0 order by CASE WHEN opportunities.status = 2 THEN 0
            WHEN opportunities.status = 1 THEN 1
            ELSE 2 END ASC, "published_date" desc