Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,626
    Provided Answers: 1

    Unanswered: How to write a select statement to get values from multiple select groups?

    Hi,
    using DB2 v10.1 on Linux.

    I have the following data:
    Code:
    A           B
    ----------- -----------
            100           1
            100           2
            200           1
            200           2
            200           3
            300           1
            300           3
    and many more rows
    I need to write a select statement.

    General rule:
    I need to group column A and search for all of the values in B comparing to another groups of A to check if all values from B matches with any values of B of any other group A.

    Sample:
    The input into my select is A = 100. Now I need to read column B where A = 100, so I get 1 and 2. Now I need to check if 1 and 2 is already in any other combination of A group.

    So I need to check for values in column B for each of A column groups in this sample A = 200 and A = 300.

    First I check A = 200 and there column B has values 1, 2 and 3. This is OK, because in this combination it exists rows 1 and 2 (see previous paragraph where A = 100).

    Now look into A = 300 for values in column B there is only number 1 from (1 and 2 combination where A = 100). So A = 300 does not apply to my rules.

    The number of A and B columns can change any minute, in the most of the cases new rows are added into table.

    The output of the select statement should be only value:
    A
    -----
    200
    because this is the only group that has all of the values (1 and 2 in this case) from input group A = 100.

    Hope I have explained this clearly enough.

    Regards
    Last edited by grofaty; 02-17-16 at 04:04.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Should we have some following groups in the output?
    Code:
    A           B
    ----------- -----------
            400           1
            400           2
            400           3
            500           1
            600           1
            600           1
            600           2
    Regards,
    Mark.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    should be fairly easy using an EXISTS subselect. This sounds a bit like a homework assignment, so I don't mind giving you a direction to investigate, but you need to show your work before most of us will help you.
    Dave

Posting Permissions

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