Sql-server – Exclude certain results from in condition in sql

sql serversql-server-2012

I have two tables

Table 1
Id data1  data1
1  'ff'   'ffe'
2  'r'    '3'
3  'df'   'er'

Table 2
Id   WFID  data1   data2
1    12    'd'     'e'
1    13    '3'     '4f'
1    15    'e'     'dd'
2    12    'f'     'ee'
3    17    'd'     'f'

Now what I need is to join the two tables and get the data when only WFID is 12 or 17 for the record and 12 or 17 only. In this case Id number 1 has 12 but it also has 12 and 15 so it's excluded. Id number 2 and 3 are included because they have just 12 or 17.

I used the below query but it is also giving me data with id 1. Can someone please tell me what I can use to fix this.

select * from Table1
where Exists (select distinct ID from Table2 where 
    Table1.ID = Table2.ID and
    WFID in (12,17))

Thanks

Best Answer

One way to get the results you're after is to "reverse" both the EXISTS and IN operators - it's a little confusing to read, though. I also got rid of the distinct, since it's not really needed in the subquery.

SELECT * 
FROM #Table1
WHERE NOT EXISTS 
(
    SELECT ID 
    FROM #Table2 
    WHERE
        #Table1.ID = #Table2.ID 
        AND WFID NOT IN (12,17)
);

Here's the setup for the query I'm using locally:

CREATE TABLE #Table1
(
    Id int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table1
    (Id, data1, data2)
VALUES
    (1,  'ff',   'ffe'),
    (2,  'r' ,   '3'  ),
    (3,  'df',   'er' );

CREATE TABLE #Table2
(
    Id int NOT NULL,
    WFID int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table2
    (Id, WFID, data1, data2)
VALUES
    (1,    12,    'd',     'e' ),
    (1,    13,    '3',     '4f'),
    (1,    15,    'e',     'dd'),
    (2,    12,    'f',     'ee'),
    (3,    17,    'd',     'f' );