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

TABLE B

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

Output:

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.

CodePudding user response：

You can use:

SELECT a.id,
       a.company_name,
       b.region
FROM   tablea a
       INNER JOIN
       ( SELECT id,
                region,
                COUNT(DISTINCT CASE WHEN region IN ('ASIA', 'AMERICA') THEN region END)
                  OVER (PARTITION BY id) AS num_asia_america,
                COUNT(DISTINCT CASE WHEN region NOT IN ('ASIA', 'AMERICA') THEN region END)
                  OVER (PARTITION BY id) AS num_other
         FROM   tableb
       ) b
       ON (a.id = b.id
          AND num_asia_america = 2
          AND num_other = 0)

Which, for the sample data:

CREATE TABLE tablea (Id, Company_Name) AS
SELECT 1, 'ABC' FROM DUAL UNION ALL
SELECT 2, 'DEF' FROM DUAL UNION ALL
SELECT 3, 'GHI' FROM DUAL;

CREATE TABLE tableb (ID, REGION) AS
SELECT 1, 'ASIA'      FROM DUAL UNION ALL
SELECT 1, 'AMERICA'   FROM DUAL UNION ALL
SELECT 1, 'AUSTRALIA' FROM DUAL UNION ALL
SELECT 2, 'ASIA'      FROM DUAL UNION ALL
SELECT 2, 'AFRICA'    FROM DUAL UNION ALL
SELECT 3, 'ASIA'      FROM DUAL UNION ALL
SELECT 3, 'AMERICA'   FROM DUAL;

Outputs:

ID	COMPANY_NAME	REGION
3	GHI	AMERICA
3	GHI	ASIA

Update

How can I refactor the query if it is user choice to bring data for which region? In that case count can be dynamic.

If your users can dynamically pass in between 1 and 3 regions then you can use named bind variables to accept the region values:

SELECT a.id,
       a.company_name,
       b.region
FROM   tablea a
       INNER JOIN
       ( SELECT id,
                region,
                COUNT(CASE WHEN region = :region1 THEN region END)
                  OVER (PARTITION BY id) AS num_region1,
                COUNT(CASE WHEN region = :region2 THEN region END)
                  OVER (PARTITION BY id) AS num_region2,
                COUNT(CASE WHEN region = :region3 THEN region END)
                  OVER (PARTITION BY id) AS num_region3,
                COUNT(
                  CASE
                  WHEN (region <> :region1 OR :region1 IS NULL)
                  AND  (region <> :region2 OR :region2 IS NULL)
                  AND  (region <> :region3 OR :region3 IS NULL)
                  THEN region
                  END
                ) OVER (PARTITION BY id) AS num_other
         FROM   tableb
       ) b
       ON (a.id = b.id
          AND (num_region1 > 0 OR :region1 IS NULL)
          AND (num_region2 > 0 OR :region2 IS NULL)
          AND (num_region3 > 0 OR :region3 IS NULL)
          AND num_other = 0)

Or, if you want to pass in a completely dynamic list then you can define a collection:

CREATE TYPE string_list AS TABLE OF VARCHAR2(20);

Then pass the collection as a bind variable:

SELECT a.id,
       a.company_name,
       b.region
FROM   tablea a
       INNER JOIN
       ( SELECT id,
                region,
                COUNT(
                  DISTINCT
                  CASE
                  WHEN region MEMBER OF :your_collection
                  THEN region
                  END
                ) OVER (PARTITION BY id) AS num_regions,
                COUNT(
                  CASE
                  WHEN region NOT MEMBER OF :your_collection
                  THEN region
                  END
                ) OVER (PARTITION BY id) AS num_other
         FROM   tableb
       ) b
       ON (a.id = b.id
          AND num_regions = CARDINALITY(:your_collection)
          AND num_other = 0)

db<>fiddle here

CodePudding user response：

select a.ID, a.company_name, b.region
from table_a a
     inner join
     table_b b on a.ID = b.ID
where a.id in(select id from table_b where region in('ASIA','AMERICA'))
  and a.id in(select id from table_b group by id having count(distinct(region)) = 2)
order by a.id, a.region