Results 1 to 4 of 4

Thread: get records within same group and with condition

  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Exclamation get records within same group and with condition

    I have a table with following record.

    ParentID Value
    1 NULL
    1 NULL
    2 3
    2 NULL
    3 4
    3 10

    How to write a query to get records with same parentID and at least one value is NULL and NON Null..

    For example, returned record should be
    ParentID Value
    2 3
    2 NULL

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Not clear, can you explain it further. 34 and 310 have same parent 3, why is that not in result?

  3. #3
    Join Date
    Jun 2008
    Posts
    4
    Because the record has value for both. I only want to get within the group, have both NULL and value.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Not sure what database system you are using, but if it support INTERSECT then you can use the following, if not you need to do something similar.

    select * from table
    where parentid in (
    select parentid from table
    where value is null
    intersect
    select parentid from table
    where value is NOT null)

Posting Permissions

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