Select from another table and count from same table-CodePudding

Given these 2 tables, partial_match and status:

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

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

I'd like a query to return the partial_match ids where the id occurs in partial_match only once (count(id) = 1), and name has code = 'YES'. So from the data above, query would only return '2'.

CodePudding user response：

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

CodePudding user response：

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 MIN(s.code) = MAX(s.code) AND  -- only one code
       MIN(s.code) = 'YES'            -- that one code is 'YES'

CodePudding user response：

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

CodePudding user response：

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.