CODEWITHSUNDEEP
sqloracle-database
nina13
nina13

Reputation: 1

SQL - Exclude rows based on field value in another table

I am having difficulty with excluding entire rows in my inner joined table based on a field value in another table. Here's what I mean:

Distinct IDs (TableA Inner Join TableB) 

    ID    |     Date     |    Bldg_Nbr   
----------+--------------+------------------
     1    |  10/20/2014  |       92       
     2    |  10/20/2014  |       92      
     3    |  10/20/2014  |       92       
     4    |  10/20/2014  |       92       
     5    |  10/20/2014  |       92

Code Table (TableC) 

    ID    |     Date     |    Code    |    Bldg_Nbr   
----------+--------------+------------+----------------
     1    |  10/20/2014  |     11     |       92
     1    |  10/20/2014  |     02     |       45
     3    |  10/20/2014  |     15     |       85
     3    |  10/20/2014  |     95     |       66
     4    |  10/21/2014  |     11     |       92

What I want to do is exclude the ID rows in the Inner Joined table that have a code 11 with the same date and bldg number listed in the Code Table (TableC). Note that the Code Table (TableC) can have an ID with many different codes with the same date and/or bldg. In this example I would want ID 1 excluded, but not ID 4 since it has a different date.

Here's the code for the first table (TableA Inner Join TableB) which works; I just need to incorporate the TableC constraints:

SELECT DISTINCT 
          I.DATE,
          I.BLDG_NBR,
          I.ID
FROM TableA I 
INNER JOIN TableB D ON I.ID = D.ID  
WHERE I.DATE = '20-OCT-2014' AND I.BLDG_NBR = 92

Upvotes: 0

Views: 2525

Answers (2)

Multisync
Multisync

Reputation: 8797

SELECT DISTINCT I.DATE, I.BLDG_NBR, I.ID
FROM TableA I
     INNER JOIN TableB D ON I.ID = D.ID
WHERE I.DATE = TO_DATE('20-10-2014', 'DD-MM-YYYY') AND I.BLDG_NBR = 92 
  AND NOT EXISTS (SELECT 1 FROM TableC C WHERE C.ID = I.ID AND I.DATE = C.DATE AND I.BLDG_NBR = C.BLDG_NBR AND C.CODE = 11)

Upvotes: 0

Kirt Hall
Kirt Hall

Reputation: 23

This will exclude code 11 rows in the Inner table that have the bldg_nbr and date in the code table:

SELECT DISTINCT 
    I.DATE, 
    I.BLDG_NBR, 
    I.ID    
FROM TableA I INNER JOIN TableB D 
    ON I.ID = D.ID
    AND I.Bldg_Nbr = D.Bldg_Nbr
    AND I.Date = D.Date
    AND D.Code != 11

WHERE I.DATE = '20-OCT-2014' 
AND I.BLDG_NBR = 92

Upvotes: 0

Related Questions