SQL get record when finding in 2 records-CodePudding

Hi I tried to build sql query to find id when is in 2 records (can be more). Let me explained by example

I have 2 tables

id	type_id
1	499
1	599

type_id	type_name
499	AN
599	DE

And I want to get id which has AN and DE


SELECT *
FROM C 
INNER JOIN  D
    ON D.type_id = C.type_id
WHERE
    EXISTS (SELECT 1 FROM D D1 WHERE D1.type_id = C.type_id AND D1.type_name = 'AN') AND 
    EXISTS (SELECT 1 FROM D D2 WHERE D2.type_id = C.type_id AND D2.type_name = 'DE');

But did not work .Than you for help

CodePudding user response：

If you want all the data from the join then you can use analytic functions:

SELECT id,
       type_id,
       type_name
FROM   (
  SELECT c.id,
         c.type_id,
         d.type_name,
         COUNT(CASE d.type_name WHEN 'AN' THEN 1 END) OVER (PARTITION BY c.id)
           AS num_an,
         COUNT(CASE d.type_name WHEN 'DE' THEN 1 END) OVER (PARTITION BY c.id)
           AS num_de
  FROM   C 
         INNER JOIN  D
         ON D.type_id = C.type_id
  WHERE  d.type_name IN ('AN', 'DE')
)
WHERE  num_an > 0
AND    num_de > 0;

Which outputs:

ID	TYPE_ID	TYPE_NAME
1	599	DE
1	499	AN

If you just want the id then you can aggregate and use a HAVING clause:

SELECT c.id
FROM   C 
       INNER JOIN  D
       ON D.type_id = C.type_id
WHERE  d.type_name IN ('AN', 'DE')
GROUP BY c.id
HAVING COUNT(CASE d.type_name WHEN 'AN' THEN 1 END) > 0
AND    COUNT(CASE d.type_name WHEN 'DE' THEN 1 END) > 0

Which outputs:

ID
1

fiddle

CodePudding user response：

Get the distinct counts of type_name for each ID ensure count = two and limit type_name to 'AN' or 'DE'

SELECT C.ID
FROM C
INNER JOIN D
 on C.type_id=D.type_id               -- standard join on Type_ID
WHERE D.Type_name in ('AN','DE')      -- limit records to only AN/DE since we need both.
GROUP BY C.ID                         -- group so we get just 1 ID
HAVING Count(Distinct Type_name) = 2  -- ensure distinct count is 2 for each C.ID.

We join the two tables
We limit to ID having either an 'AN' or DE type name
We group by ID's
We count the distinct types for each ID and if it's 2, we know we have an AN and DE type for that ID.

Count distinct is used since I'm unsure if a type_name could be duplicated for a C.ID. It looks possible given table structure. but unsure without known Pk/FK relations. distinct "Might" be able to be removed if we KNOW it's not possible.