SQL-Query: EXISTS in Subtable

I have two tables tabData and tabDataDetail. I want all idData(PK) from Parent-Table(tabData) that have only rows in Child-Table(tabDataDetail, FK is fiData) with:

• fiActionCode=11 alone or
• fiactionCode=11 and fiActionCode=34

Any other combination is invalid. How to get them?

What i've tried without success(slow and gives me also rows that have only fiActioncode 34) :

_{(source: bilder-hochladen.net)}

Thanks for your Time.

EDIT: Thanks to all for their answers. Now i unfortunately have not enough time to check which one is best or works at all. I marked the first working one as a开发者_开发技巧nswer.

EDIT2: i think that the marked answer is really the most efficient and compact solution.

EDIT3: Codesleuth's answer is interesting because it returns only rows than have only a single fiActionCode=11. Difficult to see, because that its only true for 20 tabDataDetail-rows ot of 41524189 total-rows that have two. Anyway that was not 100% what i've asked or rather what i was looking for.

Select ...
From tabData As T1
Where Exists    (
                Select 1
                From tabDataDetail As TDD1
                Where TDD1.fiData = T1.idData
                    And TDD1.fiactionCode = 11
                )
    And Not Exists    (
                      Select 1
                      From tabDataDetail As TDD1
                      Where TDD1.fiData = T1.idData
                          And TDD1.fiactionCode Not In(11,34)
                    )

To expand on my logic, the first check (a correction) is to ensure that a row with fiActionCode = 11 exists. The second check works by first defining the set of rows we do not want. We do not want anything that is something other than fiActionCode = 11 or 34. Because that is the set of items we do not want, we search for anything that does not exist in that set.

Reasoning

1. LEFT OUTER JOIN excludes all idData's that have an id different from 11 or 34
2. HAVING excludes all idData's that only have a 34
3. Remaining records (should) satisfy all constraints

Test data

DECLARE @tabData TABLE (idData INTEGER)
DECLARE @tabDataDetail TABLE (fiData INTEGER, fiActionCode INTEGER)

INSERT INTO @tabData VALUES (1)
INSERT INTO @tabData VALUES (2)
INSERT INTO @tabData VALUES (3)
INSERT INTO @tabData VALUES (4)
INSERT INTO @tabData VALUES (5)

/* Only idData 1 & 2 should be returned */
INSERT INTO @tabDataDetail VALUES (1, 11)
INSERT INTO @tabDataDetail VALUES (2, 11)
INSERT INTO @tabDataDetail VALUES (2, 34)
INSERT INTO @tabDataDetail VALUES (3, 99)
INSERT INTO @tabDataDetail VALUES (4, 11)
INSERT INTO @tabDataDetail VALUES (4, 99)
INSERT INTO @tabDataDetail VALUES (5, 34)

Query

SELECT  *
FROM    @tabData d
        INNER JOIN @tabDataDetail dd ON dd.fiData = d.idData
        INNER JOIN (
          SELECT  idData
          FROM    @tabData d
                  INNER JOIN @tabDataDetail dd ON dd.fiData = d.idData
                  LEFT OUTER JOIN (
                    SELECT  fiData
                    FROM    @tabDataDetail
                    WHERE   fiActionCode NOT IN (11, 34)
                  ) exclude ON exclude.fiData = d.idData
          WHERE   exclude.fiData IS NULL                
          GROUP BY
                  idData
          HAVING  MIN(fiActionCode) = 11        
        ) include ON include.idData = d.idData

Edited my answer based on clarification given in comments on other answers.

select td.idData
 from tabData td
  left join tabDataDetail tdd
   on td.idData = tdd.fiData
    and tdd.fiActionCode = 11
  left join tabDataDetail tdd2
   on td.idData = tdd2.fiData
    and tdd2.fiActionCode = 34
  left join tabDataDetail tdd3
   on td.idData = tdd3.fiData
    and tdd3.fiActionCode not in (11,34)
 where (tdd.fiData is not null
  or (tdd.fiData is not null and tdd2.fiData is not null))
  and tdd3.fiData is null
 group by td.idData

Edit : Apols - I see what you mean with child rows. This isn't particular efficient. Thanks also to Lieven for the data.

SELECT idData FROM
tabData td
WHERE EXISTS 
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode = 11
 )
AND NOT EXISTS
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode <> 11
 )
UNION
SELECT idData 
    FROM tabData td
    WHERE EXISTS 
    (
        SELECT 1 
            FROM tabDataDetail tdd 
            WHERE tdd.fiData = td.idData AND fiActionCode = 11
     )
    AND EXISTS
    (
        SELECT 1 
            FROM tabDataDetail tdd 
            WHERE tdd.fiData = td.idData AND fiActionCode = 34
     )
AND NOT EXISTS
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode NOT IN (11, 34)
 )

Thanks @Lieven for the data code to test this:

DECLARE @tabData TABLE (idData INTEGER)
DECLARE @tabDataDetail TABLE (idDataDetail int IDENTITY(1,1),
    fiData INTEGER, fiActionCode INTEGER)

INSERT INTO @tabData VALUES (1)
INSERT INTO @tabData VALUES (2)
INSERT INTO @tabData VALUES (3)
INSERT INTO @tabData VALUES (4)
INSERT INTO @tabData VALUES (5)

/* Only idData 1 & 2 should be returned */
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (1, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (2, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (2, 34)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (3, 99)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (4, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (4, 99)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (5, 34)

Query:

SELECT  td.idData
FROM    @tabData td
        INNER JOIN @tabDataDetail tdd ON td.idData = tdd.fiData
WHERE   tdd.fiActionCode = 11 -- check 11 exists
        AND NOT EXISTS ( SELECT * FROM @tabDataDetail WHERE fiData = td.idData
                          AND idDataDetail <> tdd.idDataDetail )
            -- ensures *only* 11 exists (0 results from subquery)
UNION
SELECT  td.idData
FROM    @tabData td
        INNER JOIN @tabDataDetail tdd1 ON td.idData = tdd1.fiData
        INNER JOIN @tabDataDetail tdd2 ON td.idData = tdd2.fiData
WHERE   tdd1.fiActionCode = 11 -- check 11 exists
        AND tdd2.fiActionCode = 34 -- check 34 exists

Returns:

idData
-----------
1
2

(2 row(s) affected)

With only 1 subquery here (and it being a COUNT instead of a very-slow NOT EXISTS) this creates a very neat execution plan which should help if you're having problems with speed.

This does it with one pass through the data I think.

It depends on the data distribution whether or not that would be preferable to doing 2 separate lookups.

WITH matches AS
(
SELECT fiData
FROM tabDataDetail 
GROUP BY fiData
HAVING COUNT(CASE WHEN fiactionCode = 11 THEN 1 END) > 0
AND COUNT(CASE WHEN fiactionCode NOT IN (11,34) THEN 1 END) = 0
)
SELECT ...
FROM idData i
JOIN matches m
ON  m.fiData = i.idData