skip usernames that have a 1 or 0 in a group

[aspnet98]

Is it possible in a view to skip usernames that have a 1 or 0?

username id week value
jay 1 1 1
jay 1 1 0
jay 1 1 1
jay 1 1 0
jim 1 1 4
jim 1 1 4
jim 1 1 4
jim 1 1 4
jane 2 1 1
jane 2 1 4
jane 2 1 4
jane 2 1 4

For example we would completely skip jay and jane in this example above and not return any of their records for that username .

The week will always be the same number. The could be the same username per ID. I want to skip the records per username per ID when they have just ONE 1 or 0 in the value field.

Is this possible to do?

 

[jbenson001]

Where ID NOT IN (0,1) ???

 

[vongrunt]

> For example we would completely skip jay and... I want to skip the records per username per ID when they have just ONE 1 or 0 in the value field.

Jay has two 1's and two 0's so... what would we do about him?

Btw. to display groups without any 1 or 0:

select * 
from myTable
where username not in (select username from myTable where value in (0, 1))

That's the query I'm currently capable of (early morning decaf :X).

Btw2. what happened with ranking by group (thread183-1116532)? Things worked or not?

------
"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)

 

[amrita418]

If I understand you requiement correctly, you do not want to display username/id where the value is 1 or 0.

Which means If Jay have another ID 2 without 0 or 1 you want that in your result set?

Here is something to start with if that indeed is your requirement:

select *
from   YourTable a,
       (select distinct 
               username, 
               id
        from   YourTable 
        where  value in (0, 1)) b
where  not (a.username = b.username and a.id = b.id)

Regards,
AA