In my system one order can have associated X documents, and each documents have an document code.

One example of my schema with data:

The table of the orders is PDOCAS and the documents save in the table DOCCAB.

I would like when join the orders with the documents, that if one of the types of documents is 1DE, do not bring the order.

select p.DocCabIdDeb as 'Order', d.DocCabId as 'Document', d.DocCod as 'Code Doc' 
from PDOCAS p 
JOIN DOCCAB d on p.DocCabIdHab=d.DocCabId
WHERE NOT EXISTS(select * from DOCCAB ds where ds.DocCabId=d.DocCabId and doccod='1DE')
and p.DocCabIdDeb in (1, 2)

In this case, it returns me the order 1 and the 5E document codes, and I don't want it to return it because one of the documents of order 1 is 1DE.

Should I join the tables in another way?

Thanks.

CodePudding user response：

I made few assumptions on the data
You need an inner query or CTEs which filter out the orders containing the blacklisted Doc codes

So you need a distinct order where document code in '1de' and then you should filter out the orders from original table with an order_id not in condition.

Below is an example query with CTE

WITH
-- Setting up the data 
PDOCAS AS (
    SELECT * FROM (
        VALUES
        (1, 101),
        (1, 102),
        (1, 103),
        (2, 201)
    ) t(DocCabIdDeb, DocCabIdHab)
),

DOCCAB AS (
    SELECT * FROM (
    VALUES
        (101, '5E'),
        (102, '5E'),
        (103, '1DE'),
        (201, '5E')
    ) t(DocCabId, DocCod)
),
-- Data setup ends

-- Your actual query starts from here
ORDERS AS (
    select 
        p.DocCabIdDeb as "OrderId",
        d.DocCabId as "Document", 
        d.DocCod as "CodeDoc" 
    from 
        PDOCAS p
        JOIN DOCCAB d on p.DocCabIdHab=d.DocCabId
    WHERE
        p.DocCabIdDeb in (1, 2)
),

ORDERS_WITH_BLACKLISTED_DOCCOD AS (
    SELECT DISTINCT 
        o.OrderId 
    FROM 
        ORDERS o
    WHERE
        o.CodeDoc in ('1DE') 
),

FINAL_ORDERS AS (
    SELECT 
        *
    FROM
        ORDERS o
    WHERE
        o.OrderId NOT IN (SELECT * FROM ORDERS_WITH_BLACKLISTED_DOCCOD)
)

SELECT * FROM FINAL_ORDERS