[SQL] self join 해결하기 (부모의 형질을 모두 가지는 대장균 찾기)

부모의 형질을 모두 가지는 대장균 찾기

이 ecoli_data table로만 각 id의 형질과 부모의 형질을 비교해야 한다.
두개의 다른 테이블을 join 하는 것보다 더 어려운 것 같다. 🥲

< 생각 하기 >

• 예시로 추출해야하는 것 생각해보기
  -- id=2.parent_id = 1.genotype << id가 2일때 부모의 id 추출(1)하고, 부모의 id(1)에 해당하는 genotype과 id의 genotype이 같은지 확인해야 한다.
  -- id=3.parent_id = 1.genotype
  -- id=4.parnet_id = 2.genotype

1) join 위한 조건 생각하기

• parent_id에 id의 genotype 붙이기를 해야 하는 군.!!

2) join 후 추출조건 생각하기

• 결과적으로 자기 genotype & 부모 genotype = 부모형질 인거 / 2 3 7 8

step1 ) parent정보가 될 table 추출

• id와 genotype만 추출한다.

select id, genotype as parent_genotype
from ecoli_data

step2&3 ) join절로 parent_id에 정보를 붙인다 & where절에 비교조건을 붙인다.

• 원본 Table의 parent_id = parent_table의 id

with geno as (
select id, genotype as parent_genotype
from ecoli_data)
select e.id, e.genotype ,g.id, g.parent_genotype 
from ecoli_data e
join geno g on e.parent_id = g.id
where ( e.genotype & g.parent_genotype) = g.parent_genotype
order by e.id

g.id는 잘 뽑혔나 확인용으로 찍어봤다.

최종코드

with geno as (
select id, genotype as parent_genotype
from ecoli_data)
select e.id, e.genotype , g.parent_genotype 
from ecoli_data e
join geno g on e.parent_id = g.id
where ( e.genotype & g.parent_genotype) = g.parent_genotype
order by e.id