Counting two tables with several conditions

Question

I have two tables,

fenotipos

id           DTHHRDX sex
GTEX-1117F   0       2
GTEX-ZE9C    2       1
K-562        1       2

atributos

SAMPID                         SMTS
K-562-SM-26GMQ                 Blood vessel
K-562-SM-2AXTU                 Blood_dry
GTEX-1117F-0003-SM-58Q7G       Brain
GTEX-ZE9C-0006-SM-4WKG2        Brain
GTEX-ZE9C-0008-SM-4E3K6        Urine
GTEX-ZE9C-0011-R11a-SM-4WKGG   Urine

I need to know how many women (sex=2) have DTHHRDX = 1 and have blood on SMTS.

For instance, the answer in this case would be 2

Answer 1

You could do:

select count(*) as cnt
from fenotipos f
where 
    sex = 2
    and exists (
        select 1
        from atributos a
        where a.sampid like concat(f.id, '%') and a.smts like 'Blood%'
    )

This properly handles potential multiple matches in atributos

Alternatively, you could join:

select count(distinct f.id) as cnt
from fenotipos f
inner join atributos a on a.sampid like concat(f.id, '%')
where f.sex = 2 and a.smts like 'Blood%'

If there are no duplicates, then count(*) is more efficient than count(distinct f.id) in the second query.