SQL retrieve rows based on column condition

Question

Say I have below tables

Order Table

OrderNo  CategoryID  CountryID  ServiceTypeID
 100         1         3             1
 200         2         5             2
 300         3         4             4
 400         1         2             9

1 service Type might belong to many category type

Category Table

 ID   Name  ServiceTypeID
 1     x        1
 2     x        2
 3     x        1

ServiceType table

 ID    Name
 1     xx
 2     xx
 3     xx

Tracking Table

OrderNo  CountryID   TrackingTypeID
 100        2             3
 200        1             4
 100        3             2
 400        5             1
 200        2             6

Reviewd Table

OrderNo 
 300
 100
 200

I want to write a query with below requirements

Order must belong to serviceTypeID = 1 or 2

And If the orderNo has a categoryID = 1

I want that record to be retrieved only if there's a record in tracking table for that orderNo with same countryID as in Order table

and if that orderNo doesn't have tracking type of id (0,7,1) in tracking table

Else for all other orders with any other category excluding orders which are not belong to serviceTypeID = (1,2) I want that record to be retrived only if there's an existing record for that orderNo in Reviewed table

and if that orderNo doesn't have tracking type of id (0,7,1) in tracking table

So basically based on above requirements the result should look like

OrderNo   CategoryID  StationID   
 100         1           3          
 200         2           5



select DISTINCT top 10000  o.orderNo , o.categoryID , o.serviceTypeid 
,o.countrtId , Tracking.countryId
from Order o
join Tracking on o.orderNo = Tracking.orderNo 

where

(o.CategoryID in (1 ) and o.countryId = Tracking.countryId 
       and
       exists (select 1
               from tracking t
               where t.orderNo = o.orderNo and t.countryId  = 
               o.countryId 
              )
      )

  OR
  (o.categoryID  in (select id from Category where ServiceTypeid in (7,8) and 
    ID not in (56 , 65)
    ) and
    exists (select 1
           from Reviewed r
           where r.orderNo = o.orderNo 
          )
  ) 

 AND not exists
                ( select 1
                 from tracking t
                 WHERE T.orderNo = o.orderNo 
                 and t.TrackingTypeID in (0 , 7 ,25))

That query seems to return only orders with ID 1 and even if it's have a trackingTypeID = 0,7,25

Answer 1

You could use exists and boolean logic:

select o.*
from orders o
where 
    (
        category_id = 1 
        and exists (select 1 from tracking t where t.order_no = o.order_no and t.country_id = o.country_id)
    )
    or (
        category_id = 2
        and exists (select 1 from reviewed r where r.order_no = o.order_no)
    )