Joined select assuming criteria given in the next record

Question

I have a SQL Server 2012 table with hundreds thousands rows of geology data (please refer to the screenshot below). These are records of gCodes for different CountryIDs.

I need a joined-select of all gCodes for CountryID = 112 assuming the following criteria:

• Xc for CountryID=61 is significantly greater than Xm. At least 200%
• Xm and Xc for CountryID=112 is equal.

There are two examples on the screenshot provided. In both cases:

• Xm = Xc = 1395 for CountryID=112
• For CountryID=61 Xc is significantly greater than Xm for the same gCode

Which means that I want these two records into the recordset.

I understand that my query must somehow do the inner join ... on gCode within the same table but so far, I have no idea how to write the query. I wrote some queries but it did not bring the desired result.

Answer 1

This is a direct translation into SQL:

with cte1 as
 ( select gCode 
   from tab
   where CountryID = 112
     and xm = xc -- Xm and Xc for CountryID=112 is equal.
 ),
cte2 
 ( select gCode 
   from tab
   where CountryID = 61
     and xc/xm >= 3 -- Xc for CountryID=61 is significantly greater than Xm. At least 200%
 )
select cte1.gCode
from cte1
join cte2
on cte1.gCode = cte2.gCode

I would prefer a correlated subquery:

select gCode 
from tab as t1
where CountryID = 112
and xm = xc -- Xm and Xc for CountryID=112 is equal.
and exists
 ( select * 
   from tab as t2
   where t2.gCode = t1.gCode
     and CountryID = 61
     and xc/xm >= 3 -- Xc for CountryID=61 is significantly greater
 )