Find Group Where All Members Are Denied... 1

[hneal98]

Can someone tell me how to do the following?

I have a group of records where there is a header table and a detail table. The header and detail share a common ID, of course. The detail table has a status field where it can be ok or deny. I want to find all records in the header where all members in the detail are deny. Here is an example...

idnum linenum status
1 0 ok
1 1 ok
1 2 deny
2 1 ok
2 2 ok
3 1 deny
3 2 deny
3 3 deny
4 0 ok
4 1 deny
4 2 ok
5 3 deny
5 4 deny
5 5 deny

As you can see, I would want IDNum 3 and 5 in this case.

Let me know if you need any more info.

Thanks ahead of time for any help.

Harv

 

[gmmastros]

If OK and DENY are the only 2 values in the status column, then this query should work out for you.

[COLOR=blue]Select[/color]   IdNum
[COLOR=blue]From[/color]     [!]TableName[/!]
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] IdNum
[COLOR=blue]Having[/color]   [COLOR=#FF00FF]Count[/color](idnum) = [COLOR=#FF00FF]Count[/color]([COLOR=#FF00FF]NullIf[/color](Status, [COLOR=red]'ok'[/color]))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Wouldn't you want idnum 1 as well? How does this work for you?

SELECT idnum
FROM header h
JOIN detail d
ON h.idnum = d.idnum
WHERE d.status = 'deny'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Did I miss something? Shouldn't you get 1,3,4 and 5

idnum linenum status
1 0 ok
1 1 ok
1 2 deny
2 1 ok
2 2 ok
3 1 deny
3 2 deny
3 3 deny
4 0 ok
4 1 deny
4 2 ok
5 3 deny
5 4 deny
5 5 deny

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Paul.

The Original Poster wants to know which IdNum's have 'deny' for all records, not just one.

Here's another query that can be used in case there are other values in the status column.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](idnum [COLOR=blue]int[/color], linenum [COLOR=blue]int[/color], status [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,0,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,1,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,2,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,1,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,2,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3,1,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3,2,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3,3,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4,0,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4,1,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4,2,[COLOR=red]'ok'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5,3,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5,4,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5,5,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6,3,[COLOR=red]'deny'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6,4,[COLOR=red]'Something Else'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6,5,[COLOR=red]'deny'[/color])

[COLOR=blue]Select[/color]   IdNum
[COLOR=blue]From[/color]     @Temp
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] IdNum
[COLOR=blue]Having[/color]   [COLOR=#FF00FF]Count[/color](idnum) = Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Status = [COLOR=red]'deny'[/color] [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Oohhhhh! <hang head in shame> Sorry! </hang head in shame>


I did miss something.

where all members in the detail are deny

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[hneal98]

Thanks everyone. GMMastros, your first query didn't quite work, but it looks like your second query is working. I appreciate all the help.

ptheriault, don't feel bad, I do that quite often myself.

Harv