CODEWITHSUNDEEP
sqloracle-databaseselect-query
Marcus
Marcus

Reputation: 3869

Check combination of values exist in table using Select query Oracle

I have below query which is not returning expected output result. For NAME column with value='PL' it should check the data in M_LOG table with NAME and E_ID column values combination and check if this combination value exist in DIR_LOG table. If it does not exist the query should return only those combination values.

Currently the query is returning all the combination value which is already exist in M_LOG table. I think i am missing small condition somewhere is query.

Select MAX(ML.NAME), ML.E_ID,  CASE   --If the day of the month is the 1st, 2nd or 3rd then it will use the last day of the previous month otherwise it will use the last day of the current month
                WHEN EXTRACT( DAY FROM SYSDATE ) <= 3
                THEN TRUNC( SYSDATE, 'MM' ) - INTERVAL '1' DAY
                ELSE LAST_DAY( TRUNC( SYSDATE ) ) END, 1, 'M1' from DIR_LOG ML, M_LOG MD
WHERE ML.NAME != MD.NAME and ML.E_ID != MD.E_ID and
ML.NAME = 'PL' 
GROUP BY ML.E_ID

Upvotes: 1

Views: 991

Answers (1)

Patrick Bacon
Patrick Bacon

Reputation: 4640

Query Similar to "all depts not having employees"

A common approach to this problem is to use a correlated subquery. In simpler terms using the sample schema, scott, tables, here is an example:

    SELECT
    d.deptno
FROM
    dept d
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            emp e
        WHERE
            e.deptno = d.deptno
    );

as opposed to an approach like this (some correspondence to your approach): 

SELECT
    d.deptno
FROM
    dept d
    JOIN emp e ON d.deptno != e.deptno;

You would need take an approach like this:

SELECT
    MAX(ml.name),
    ml.e_id,
    CASE   --If the day of the month is the 1st, 2nd or 3rd then it will use the last day of the previous month otherwise it will use the last day of the current month
            WHEN EXTRACT(DAY FROM SYSDATE) <= 3 THEN trunc(SYSDATE,'MM') - INTERVAL '1' DAY
            ELSE last_day(trunc(SYSDATE) )
        END,
    1,
    'M1'
FROM
    dir_log ml
WHERE
    1 = 1
    AND   ml.name = 'PL'
NOT EXISTS (
    SELECT
        1
    FROM
        m_log md
    WHERE
        ml.name = md.name
        AND   ml.e_id = md.e_id
)
GROUP BY
    ml.e_id,
    CASE
            WHEN EXTRACT(DAY FROM SYSDATE) <= 3 THEN trunc(SYSDATE,'MM') - INTERVAL '1' DAY
            ELSE last_day(trunc(SYSDATE) )
        END,
    1,
    'M1'
    ;

The group by has been modified to include all non-aggregate values in the selection.

Upvotes: 1

Related Questions