SQL get record when finding in 2 records

M.B 0
Viewed 31

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

C

id type_id
1 499
1 599

D

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

oracle sql
2 Answers

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

MT00

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.

xQbert0
Related