EASY Query help...

[multiplexor]

Hey all :-)

I need help with a query and hope I can get some quick help? :-)

Basically I have this data:

USER_ID, DATA_ID
---------------------
1, 0
1, 1
2, 0
2, 0
3, 1
3, 1

the first column is a bunch of userids... the second one is either 0 or 1 or both 0 or both 1... :-/

I want to be able to extract users who have only zero's in the DATA_ID. In the case above, that would be USER_ID = 2....

Any idea how i could write a query around that?

Note that to reach that state, I had to write other queries under it... Basically column 2 in my case was achieved by doing a case. if ID1 matches ID2 from another column then display 1, else 0... Thus if initially i had a user_id of 1 with a data id of 555 in 1 table and 556 in a second table then you would see "0" above (like you do now) just as the next data id for the "1" user_id is 555, thus the "1" in the column above.... I'm doing it like this because as you can tell my sql skills are not the best....

So i'm now at a point where you see the data above, but would like to only return (in the case above) the row where the user_id is "2" because it ONLY has zero data_id's...

anyone have a quick solution to this?

Thanks!!

[ebrian]

What have you tried already?

[hrishy]

Hi

Is this what you are looking for

SELECT user_id,max_date_id
FROM  (
SELECT userid,MAX(data_id) max_date_id
		OVER(PARTITION by user_id)
FROM my_table
)
where max_date_id=0

[LKBrwn_DBA]

Or maybe just:

Select User_Id, Sum(Data_Id) From My_Table
 Group By User_Id Having Sum(Data_Id) = 0;

[multiplexor]

I'm useing sybase and the OVER PARTITION option didn't seem to work...

However I thought about and realized that primarily i really just want to remove from the master list, any instance where the user has a "1"...

thus I did a select * from that list i gave... so it returned combinations of zero and ones... but then i simply did an NOT in (select * from that same list)

So basically i removed the id's via the NOT IN option.

That said, I was moved off the project :-/ It was the final task that had been given to me....

Thanks for the replies... though feel free to keep replying in case others might ever run into a similar situation...

[PAVB]

So you are using Sybase... asking questions in an Oracle forum? isn't that cute?

Don't worry, by being removed from the project your boss managed to get your best contribution to it.

[multiplexor]

So you are using Sybase... asking questions in an Oracle forum? isn't that cute?

Don't worry, by being removed from the project your boss managed to get your best contribution to it.

lol... ouch? ;-)