How to edit EXISTS query

Question

I have a query that has EXISTS query, which has PRCS_STE_CD column. QUERY

SELECT          
    PRCS_SNO
          , YEAR
          , SUBSC_CANC_YN                             
    FROM
        TB_POT_ECD_PRCS_INFO INF
    WHERE
        INF.SUBSC_CANC_YN = 'N'
        AND EXISTS (
                   SELECT 'X'
                   FROM TB_POT_ECD_PRCS_HIST HIS
                   WHERE PRCS_STE_CD = 'R01'
                   )

Idea is to show only ones that PRCS_STE_CD = 'R01'.

But the problem is that some have 'R01' and 'R02' for PRCS_STE_CD.

(In this case, where it has both R01 and R02, I do not want to show it on the list.)

Basiacally, I want to show one that only has R01 for PRCS_STE_CD.

Oh and PRCS_STE_CD CAN NOT have only R02. It must have R01 in order to have R02.

And again, when it has both R01, R02, it should not be selected on the list.

Can anyone help editing the query?

Perhaps you should explain the the problem you are trying to solve, give example data, and desired results. — Gordon Linoff, Dec 24 '13 at 03:02
Yes, it sounds like all you need is a double left-join / where applied, but what is the relationship of the outer record to that of the inner. The inner has no correlation and just returns a set regardless of outer record. — DRapp, Dec 24 '13 at 03:04
http://stackoverflow.com/questions/9023879/sql-update-statement-with-where-exists — , Dec 24 '13 at 03:15

score 3 · Accepted Answer · edited Dec 24 '13 at 04:39

In your table how have you stored R02 for PRCS_STE_CD. I assume you have a different column to store R02. If so then you can try using the IN and the OR Operators as.

SELECT      
    PRCS_SNO, YEAR, SUBSC_CANC_YN                             
    FROM TB_POT_ECD_PRCS_INFO INF
    WHERE INF.SUBSC_CANC_YN = 'N'
    AND 'X' IN (
                SELECT 'X'
                FROM TB_POT_ECD_PRCS_HIST HIS
                WHERE PRCS_STE_CD = 'R01' OR Second_Column= 'R02'
               )

How to edit EXISTS query

1 Answers1