JOIN Two tables in SQL with one to many mapping and do not join for an Id if any one of the row does not statisfy the condition

Question

I have to join 2 tables with one to many mapping. I have to select the rows for an Id of all rows satisfy the condition or else do not select it.

Example:

Table A 
Id Company_Name
 1 ABC 
 2 DEF 
 3 GHI
 4 JKL

---

TABLE B
ID REGION   BRANCH Number
1 ASIA      1
1 AMERICA   1
1 AUSTRALIA 2
2 ASIA.     1
2 AFRICA.   2
3 ASIA.     3
3 AMERICA.  3
4.ASIA.     1
4. ASIA.    2
4 ASIA.     3

Here I want to join table A and table B only when the company of present in both asia and America only.

Output:

ID company_name region branch_number
3. GHI Asia  3
3. GHI America. 3
4. JKL ASIA.  1
4. JKL ASIA.  2
4. JKL ASIA.  3

• It should not select ID 1 since it is also present in Australia.
• It should also not select 2 as it is not present in America.
• It selects 3 as ASIA and AMERICA is present.
• IT selects 4 AS ASIA is present.

Answer 1

Here's one option; subquery calculates whether certain ID has any rows for regions not begin Asia and America (for them, sumreg value is larger than 0 so you omit them from the final result).

Sample data:

SQL> with
  2  a (id, company_name) as
  3    (select 1, 'abc' from dual union all
  4     select 2, 'def' from dual union all
  5     select 3, 'ghi' from dual union all
  6     select 4, 'jkl' from dual
  7    ),
  8  b (id, region, branch) as
  9    (select 1, 'asia'     , 1 from dual union all
 10     select 1, 'america'  , 1 from dual union all
 11     select 1, 'australia', 2 from dual union all
 12     select 2, 'asia'     , 1 from dual union all
 13     select 2, 'africa'   , 2 from dual union all
 14     select 3, 'asia'     , 3 from dual union all
 15     select 3, 'america'  , 3 from dual union all
 16     select 4, 'asia'     , 1 from dual union all
 17     select 4, 'asia'     , 2 from dual union all
 18     select 4, 'asia'     , 3 from dual
 19    )

Query begins here:

 20  select b.id, a.company_name, b.region, b.branch
 21  from a join b on a.id = b.id
 22         join -- the C subquery returns only IDs that are valid only for Asia and America
 23              (select x.id,
 24                      sum(case when x.region not in ('asia', 'america') then 1 else 0 end) sumreg
 25               from b x
 26               group by x.id
 27              ) c on c.id = b.id and c.sumreg = 0;

        ID COM REGION        BRANCH
---------- --- --------- ----------
         3 ghi america            3
         3 ghi asia               3
         4 jkl asia               3
         4 jkl asia               2
         4 jkl asia               1

SQL>