CODEWITHSUNDEEP
sqloracle-database
Chris C
Chris C

Reputation: 51

Select from another table and count from same table

Given these 2 tables, partial_match and status:

   partial_match p      
    id | name
    ----------
    1  | a
    1  | b
    2  | c
    3  | d
    4  | e

   status s
    name | code
    ------------
    a    | YES
    b    | NO
    c    | YES
    c    | MAYBE
    d    | NO
    d    | MAYBE
    e    | YES

I'd like a query to return the partial_match ids where the id occurs in partial_match only once, and name has a code with 'YES' in status. So from the data above, query would return ids '2' and '4'.

Upvotes: 1

Views: 75

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

I would use aggregation here:

SELECT p.id
FROM partial_match p
INNER JOIN status s
    ON s.name = p.name
GROUP BY p.id
HAVING COUNT(DISTINCT p.name) = 1 AND
       COUNT(CASE WHEN s.code = 'YES' THEN 1 END) > 0;

Upvotes: 1

ahmed
ahmed

Reputation: 9191

You may use Count function with partition by clause to find the counts of ids from 'partial_match' table, then using Exists with a correlated subquery you can check if the id have a 'YES` value in 'status' table, check the following:

Select P.id From
(
  Select id, name, COUNT(id) Over (Partition By id) cn 
  from partial_match
) P
Where Exists(Select 1 From status S Where S.name = P.name And S.code = 'YES')
And P.cn = 1

See a demo from db<>fiddle.

Upvotes: 2

Sniper Dev
Sniper Dev

Reputation: 64

SELECT
    partial_match.id as id
FROM partial_match
JOIN status on status.name = partial_match.name and status.code = 'YES'
GROUP BY partial_match.id

Upvotes: 0

donut
donut

Reputation: 790

SELECT
    a.id AS id 
FROM 
    partial_match a
    JOIN
    status b
ON a.name=b.name AND b.code='YES'

Upvotes: 0

Related Questions