Query in a Query....

[philwes83170]

Here's my situation. I've got a few different ideas how to do this but, just can't wrap my head around the right solution.

I have a View with the following data. An AccountID, ContactID, then four Boolean (T/F) fields. I need to look at each AccountID, then all of the Contacts at once and determine if any of those boolean fields are true. If they are then I want that entire Account group thrown out. I only want to see an AccountID if all of its Contacts have False in all of the boolean fields.

Suggestions on how best to handle this?

 

[maswien]

I guess you are baffled by the detail, it can be simplified as " find the record with
the four boolean column value are false."

so:

select blah, blah...
from yourTable
where BooleanCol1 = 'false' and
BooleanCol2 = 'false' and
BooleanCol3 = 'false' and
BooleanCol4 = 'false'

 

[vongrunt]

A little bit of reversed logic should help. Try this:

select A.*
from myView A
where AccountID NOT IN
(	select AccountID
	from myView
	where b1 | b2 | b3 | b4 = 1
)

b1 - b4 are boolean (bit?) fields, | is bitwise OR operator.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

why the reversed logic? why not this --

select A.*
from myView A
where b1 | b2 | b3 | b4 = 0

r937.com | rudy.ca

 

[vongrunt]

Because of this:

I only want to see an AccountID if all of its Contacts have False in all of the boolean fields.

So I thought inverted logic could simplify things - if at least one Contact has at least one True, skip entire AccountID group.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[philwes83170]

You all have hit the point I'm at. But here's the curve ball....

If Account A has four Contacts and only one of those has a True value in boolean fields, I still get the other three records returned. I don't want that, I only want to see an AccountID if all of the contacts from that AccountID have all false values.

I need to somehow look at all of the Contacts for an ID at once. Just can't quite get the logic to work out, I figure I need to somehow put a loop within each Account or something....

 

[SQLBill]

Can you give us a sample of your data and what you want returned?

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

Lemme elaborate.

"I only want to see an AccountID if all of its Contacts have False in all of the boolean fields."

... is same as:

"I don't want to see an AccountID if some of its Contacts have some True values in boolean fields."

Therefore:

select A.* -- select rows
from myView A  -- from view
where AccountID NOT IN -- for which AccountID has no rows
(    select AccountID
    from myView
    where b1 | b2 | b3 | b4 = 1 -- with some True values in boolean fields
)

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

select A.foo
     , A.bar
     , A.qux
  from myView A
 where sum(
         case when b1 | b2 | b3 | b4 = 0 
              then 0 else 1 end
          ) < 1
group
    by A.foo
     , A.bar
     , A.qux

you need to itemize your A columns in both the SELECT and GROUP BY

r937.com | rudy.ca

 

[philwes83170]

Here's a sample my view:

Account ID ContactID Parts Training Service
A123456 C123445 T F F
A123456 C123435 F F F
A987654 C987123 F F F
A987654 C765456 F F F

Desired Results
A987654

I'm getting close to what I need from vongrunt but, I've gotten the opposite effect. Using the above example I get A123456. I tried reversing the logic a couple ways and still end up with a record for each contact. Again using above data I would get the bottom three records returned.

 

[vongrunt]

Again I don't see problems here... Run this. What did you get for result(s)?

create table blah ( AccountID char(7), ContactID char(7), Parts bit, Training bit, Service bit )
insert into blah values ('A123456', 'C123445', 1, 0, 0)
insert into blah values ('A123456', 'C123435', 0, 0, 0)
insert into blah values ('A987654', 'C987123', 0, 0, 0)
insert into blah values ('A987654', 'C765456', 0, 0, 0)

--  select A.*
select distinct A.AccountID
from blah A
where AccountID NOT IN
(    select AccountID
    from blah
    where Parts | Training | Service = 1
)

-- drop table blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Or if distinct AccountID values are all you need:

select AccountID
from blah
group by AccountID
having sum(Service + 2*(Training + 2*Parts)) = 0

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)