에러 노트: SQL 조인 위치 실수Unknown column 'h_certify.return_certkey' in 'on clause'

### 에러 노트: SQL 조인 위치 실수

#### 발생 원인:

- **조인 순서 실수**: `h_charge_complete` 테이블과 `h_certify` 테이블을 조인하는 순서가 잘못 설정되어 `Unknown column 'h_certify.return_certkey' in 'on clause'` 오류가 발생.
    - **잘못된 쿼리**:
        
        sql

SELECT 
protocol_charge_operation_list.sid 
FROM protocol_charge_operation_list 
LEFT JOIN h_charge_complete ON h_charge_complete.certify_key = h_certify.return_certkey 
LEFT JOIN h_certify ON h_certify.cert_operator_by = protocol_charge_operation_list.code

        
    - **문제**: `h_charge_complete` 테이블에서 `h_certify.return_certkey`를 참조하는데, 이때 `h_certify` 테이블이 `h_charge_complete`와 조인되기 전에 사용되고 있어서 `h_certify` 테이블의 `return_certkey` 컬럼을 찾을 수 없는 오류 발생.
    - **해결된 쿼리**:
        
        sql

SELECT protocol_charge_operation_list.sid 
FROM protocol_charge_operation_list 
LEFT JOIN h_certify ON h_certify.cert_operator_by = protocol_charge_operation_list.code 
LEFT JOIN h_charge_complete ON h_charge_complete.certify_key = h_certify.return_certkey

        
    - **문제 해결**: `h_certify` 테이블을 먼저 조인한 후, 그 결과를 사용하여 `h_charge_complete`와 올바르게 조인함.

#### 영향 범위:

- 이 오류는 `h_charge_complete`와 `h_certify` 테이블 간의 조인 순서를 잘못 설정할 경우 발생.
- `h_certify.return_certkey`를 참조하는 쿼리에서 올바른 순서로 테이블을 조인하지 않으면, SQL 실행 시 컬럼을 찾을 수 없다는 오류가 발생.

#### 주요 프로세스 영향:

1. **조인 관계 불일치**:
    - `h_charge_complete` 테이블과 `h_certify` 테이블이 올바르게 조인되지 않아서, `h_certify.return_certkey` 컬럼을 찾을 수 없는 문제가 발생.
2. **쿼리 실행 실패**:
    - SQL 쿼리가 실행되지 않거나, 결과가 반환되지 않음. 이로 인해, 데이터가 조회되지 않거나 예상된 결과를 얻을 수 없음.
3. **조인 오류로 인한 데이터 불일치**:
    - 잘못된 조인 순서로 인해 데이터가 잘못 결합되거나 누락될 수 있음.

#### 해결 방안:

1. **조인 순서 수정**:
    
    - `h_certify` 테이블을 먼저 조인하고, 그 후에 `h_charge_complete` 테이블을 조인하여 `h_certify.return_certkey` 컬럼을 정상적으로 참조할 수 있도록 해야 함.
    - SQL 쿼리에서 `LEFT JOIN` 순서를 올바르게 변경:
        
        sql

SELECT protocol_charge_operation_list.sid 
FROM protocol_charge_operation_list 
LEFT JOIN h_certify ON h_certify.cert_operator_by = protocol_charge_operation_list.code 
LEFT JOIN h_charge_complete ON h_charge_complete.certify_key = h_certify.return_certkey

        
2. **쿼리 검증**:
    
    - 수정된 쿼리가 정상적으로 실행되는지, `h_certify`와 `h_charge_complete` 간의 관계가 올바르게 설정되었는지 확인.
    - 필요에 따라, `h_certify` 테이블에서 `return_certkey` 컬럼이 정확하게 존재하는지 점검.
3. **SQL 로깅**:
    
    - SQL 쿼리 실행 전, 쿼리의 실행 계획을 확인하여 조인 순서와 테이블 간의 관계가 의도대로 설정되어 있는지 점검.
    - 조인 관계에 문제가 있을 경우, 로깅을 통해 어느 부분에서 문제가 발생했는지 파악.
4. **테스트 및 검증**:
    
    - 수정 후 쿼리를 실행하여 결과가 의도한 대로 나오는지 확인.
    - 데이터의 일관성을 체크하여, 조인 후 예상된 데이터가 정확하게 반환되는지 검증.

#### 결론:

SQL에서 테이블을 조인하는 순서를 잘못 설정하면, 참조되는 컬럼을 찾을 수 없는 오류가 발생할 수 있습니다. `h_certify` 테이블과 `h_charge_complete` 테이블을 올바른 순서로 조인하여 `h_certify.return_certkey` 컬럼을 정상적으로 참조할 수 있도록 수정해야 합니다.