SQL - Matching same value in same table, different columns

Question

I am trying to confirm that a process which sends document_id with a direction flag = 'O' and receives that same document_id back and records it in the same table, but in a new line and different column: src_document_id, with a direction = 'I'.

Need to identify records that were sent, (Direction flag = O) that do not have a matching value for the src_document_id where direction_flag = 'I'. Not sure if I use EXISTS or Not Exists when I check the Outbound value against the Inbound value.

EX in below table: Query should return Document_id 34567 as that does not have a corresponding src_document_id with Direction_flag = I.

select
    document_id
from
    DOCUMENT_TABLE 
where
    direction_flag = 'O'
    and
    client_id = '9999'
    and
    EXISTS
    (
        select
            document_id
        from
            DOCUMENT_TABLE
        where
            document_id != src_document_id and direction_flag = 'I'
    )

Hopefully, I am being clear enough. Not well versed in SQL.

DOCUMENT_ TABLE:

Document_id     client number   Direction flag    src_document_id 
12345             9999               O  
23456             9999               O
34567             9999               O  

aaaaa                                 I                12345
aaaab                                 I                23456

Dai · Accepted Answer · 2017-09-18 22:57:18Z

Correlated Subqueries generally have poor performance. I think your query would be better with a self-join subquery instead.

(Correlated Subqueries generally-speaking are subqueries that exist in either the SELECT clause or the WHERE clause and are evaluated for each row in the set, which is naturally inefficient (think: O(n*m) complexity) and doesn't always result in an optimised execution plan.)

I would have this:

SELECT
    [source].document_id
FROM
    (
        SELECT
            document_id
        FROM
            document_table
        WHERE
            direction_flag = 'O'
    ) AS [source]
    LEFT JOIN
    (
        SELECT
            document_id,
            src_document_id
        FROM
            document_table
        WHERE
            direction_flag = 'I'
    ) AS [dest]
        ON [source].document_id = [dest].src_document_id
 WHERE
    dest.document_id IS NULL

Note I'm using full queries for my subqueries as it makes the intention and differentiation between "document_table for source documents" and "document_table for destination documents" more clear compared to a more compacted query like @Donal gave in his answer - it's purely down to style and readability as both queries are correct.

Donal · Accepted Answer · 2017-09-19 14:08:47Z

0

You could join the table to itself on the id fields using a left join. This will show all the records from the left hand side and only those from the right that match. In the where clause you can filter out the records that do not match (src_document_id is null).

SELECT
d1.document_id
FROM DOCUMENT_TABLE d1
LEFT JOIN DOCUMENT_TABLE d2 ON d1.document_id = d2.src_document_id 
WHERE
d1.direction_flag = 'O' AND
d2.src_document_id IS NULL AND
d2.direction_flag = 'I'

edited Sep 19, 2017 at 14:08

answered Sep 18, 2017 at 22:53

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Comments

Rahmat Ihsan · Accepted Answer · 2017-09-19 14:30:36Z

0

Dai's answer are correct

but Donal's answer has a little mistake. For the correct answer, it should be like this :

SELECT
d1.document_id
FROM DOCUMENT_TABLE d1
LEFT JOIN DOCUMENT_TABLE d2 ON d1.document_id = d2.src_document_id 
AND d2.direction_flag = 'I'
WHERE
d1.direction_flag = 'O' AND
d2.src_document_id IS NULL

since "d2.src_document_id" is always null then the statement " d2.direction_flag = 'I' " is useless in the where condition.

answered Sep 19, 2017 at 14:30

Rahmat Ihsan

3264 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL - Matching same value in same table, different columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related