java.sql.SQLSyntaxErrorException: (conn=1144) Unknown column ~ in 'where clause' 에러

에러 메세지

java.sql.SQLSyntaxErrorException: (conn=1144) Unknown column 'F.franchise_code' in 'where clause'

원인

문제의 Mybatis 쿼리:

    <select id="selectFranExchangeList" resultMap="franExchangeListResultMap">
        SELECT
              A.exchange_code
            , B.status
            , C.order_code
            , GROUP_CONCAT(E.name SEPARATOR ', ') AS item_name
            , A.sum_price
            , A.created_at
            , CASE WHEN B.status = 'COMPLETED' THEN B.created_at ELSE NULL END AS completed_at
        FROM tbl_exchange A
              JOIN tbl_exchange_status_history B ON A.exchange_code = B.exchange_code
              JOIN (
                     SELECT exchange_code, MAX(created_at) AS max_created_at
                     FROM tbl_exchange_status_history
                     GROUP BY exchange_code
                   ) latest_status ON B.exchange_code = latest_status.exchange_code
              AND B.created_at = latest_status.max_created_at
              JOIN tbl_order C ON A.order_code = C.order_code
              JOIN tbl_exchange_item D ON A.exchange_code = D.exchange_code
              JOIN tbl_item E ON D.item_code = E.item_code
        WHERE F.franchise_code = #{ franchiseCode }
        GROUP BY A.exchange_code
        LIMIT #{ offset }, #{ pageSize };
    </select>

SQL 쿼리에서는 F.franchise_code를 WHERE 절에서 사용하고 있지만, MyBatis 쿼리에서는 tbl_franchise 테이블이 없기 때문에 발생한 문제였다. sql 쿼리를 Mybatis xml로 옮기는 과정에서 JOIN tbl_franchise F ON C.franchise_code = F.franchise_code Join 부분을 빠트렸던 것...

해결

Join문을 추가해서 해결했다.

수정한 Mybatis 코드:

    <select id="selectFranExchangeList" resultMap="franExchangeListResultMap">
        SELECT
              A.exchange_code
            , B.status
            , C.order_code
            , GROUP_CONCAT(E.name SEPARATOR ', ') AS item_name
            , A.sum_price
            , A.created_at
            , CASE WHEN B.status = 'COMPLETED' THEN B.created_at ELSE NULL END AS completed_at
        FROM tbl_exchange A
              JOIN tbl_exchange_status_history B ON A.exchange_code = B.exchange_code
              JOIN (
                     SELECT exchange_code, MAX(created_at) AS max_created_at
                     FROM tbl_exchange_status_history
                     GROUP BY exchange_code
                   ) latest_status ON B.exchange_code = latest_status.exchange_code
              AND B.created_at = latest_status.max_created_at
              JOIN tbl_order C ON A.order_code = C.order_code
              JOIN tbl_exchange_item D ON A.exchange_code = D.exchange_code
              JOIN tbl_item E ON D.item_code = E.item_code
              JOIN tbl_franchise F ON C.franchise_code = F.franchise_code
        WHERE F.franchise_code = 2
        GROUP BY A.exchange_code
        LIMIT #{ offset }, #{ pageSize };
    </select>

JOIN tbl_franchise F ON C.franchise_code = F.franchise_code 부분을 추가했다.