mysql same row different column ref.(ifnull check)

Question

I want IFNULL check in same row, but different column.

This is my sql example.

SELECT 
    a.CONTACT_SEQ,
    'TEST' AS MEMO, 
    NOW() AS REG_DATE,
    a.USER_ID,
    a.NAME,
    a.CONTACT_NUM AS PHONE_NUM,
    (SELECT COUNT(*) FROM TB_CONTACT AS a,  TB_CONTACT_GROUP AS b
        WHERE
            b.USER_ID = 'spark@naver.com'
        AND
            b.GROUP_CONTACT_SEQ = 120
        AND
            a.GROUP_CONTACT_SEQ = b.GROUP_CONTACT_SEQ
    ) AS TOTCOUNT,
    IFNULL((SELECT * FROM tb_auto_ban WHERE user_id = 
'spark0017@naver.com' AND phone_num = a.CONTACT_NUM) , 'Y','N') AS define

FROM
        TB_CONTACT AS a, TB_CONTACT_GROUP AS b
            WHERE
                b.USER_ID = 'spark@naver.com'
            AND
                b.GROUP_CONTACT_SEQ = 120
            AND
                a.GROUP_CONTACT_SEQ = b.GROUP_CONTACT_SEQ

When I use IFNULL, how can I refer same row another column?

score 1 · Accepted Answer · answered Jul 28 '14 at 05:24

You need to use EXISTS in this case:

SELECT 
    a.CONTACT_SEQ,
    'TEST' AS MEMO, 
    NOW() AS REG_DATE,
    a.USER_ID,
    a.NAME,
    a.CONTACT_NUM AS PHONE_NUM,
    (SELECT COUNT(*) FROM TB_CONTACT AS a,  TB_CONTACT_GROUP AS b
        WHERE
            b.USER_ID = 'spark@naver.com'
        AND
            b.GROUP_CONTACT_SEQ = 120
        AND
            a.GROUP_CONTACT_SEQ = b.GROUP_CONTACT_SEQ
    ) AS TOTCOUNT,
    CASE WHEN EXISTS(SELECT 1 FROM tb_auto_ban WHERE user_id = 
'spark@naver.com' AND phone_num = a.CONTACT_NUM) THEN 'Y' ELSE 'N' END AS define
FROM
        TB_CONTACT AS a, TB_CONTACT_GROUP AS b
            WHERE
                b.USER_ID = 'spark@naver.com'
            AND
                b.GROUP_CONTACT_SEQ = 120
            AND
                a.GROUP_CONTACT_SEQ = b.GROUP_CONTACT_SEQ

mysql same row different column ref.(ifnull check)

1 Answers1