DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: EASY Query help...

  1. #1
    Join Date
    Jun 2008
    Posts
    3

    EASY Query help...

    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!!

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    What have you tried already?

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Is this what you are looking for

    Code:
    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

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking

    Or maybe just:

    Code:
    Select User_Id, Sum(Data_Id) From My_Table
     Group By User_Id Having Sum(Data_Id) = 0;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Jun 2008
    Posts
    3

    hey guys :-)

    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...

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Jun 2008
    Posts
    3
    Quote Originally Posted by 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.
    lol... ouch? ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width