Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    25

    Unanswered: Different Duplicate

    We have list of Profile Id as {23,27,29,32,35,38,41,46,49,56,58,61,67). We have numerous and dynamic list of Profile id

    Using the below table : For this list Profile id =(23,29,35,41,46,49,61) the exam id are 100 and 101 ie it has duplicate pair[it can be triplets or above]. For pair/triplets exam id we have list theProfile id in the out put =(23,29,35,41,46,49,61)

    Can it done by using a single query:


    Exam_id || Profile id


    100 23
    101 23
    101 27
    100 29
    101 29
    103 32
    100 35
    101 35
    102 38
    100 41
    101 41
    100 46
    101 46
    100 49
    101 49
    101 56
    100 58
    100 61
    101 61
    100 67

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Let's be sure I understand what you wanting to get. You have a list of ids and you want to know of that list which ids have taken the same exam, but more than just an occurrence of 1 exam?

  3. #3
    Join Date
    Nov 2011
    Posts
    25
    Quote Originally Posted by DNance View Post
    Let's be sure I understand what you wanting to get. You have a list of ids and you want to know of that list which ids have taken the same exam, but more than just an occurrence of 1 exam?
    I have list of profiles in comma separated format but they are multi records. Of these profiles in multi record format I required those particular profiles which has multiple same exam ids.
    Profile in source:

    (23,29,35,41,46,49,61)
    (223,229,335,241,246,249,261)
    Last edited by pp8771; 04-02-17 at 16:03.

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    and what have you accomplished in your assignment so far? I would think two representations of the table to be queried. Perhaps in a nested table expression, you find all the exam ids that have a COUNT >= 2. then you join that with the table again, to get a distinct list of all of your ids in those exams? I would expect your list of ids to be used as a predicate in both the nested expression and in the outer part of the query.

  5. #5
    Join Date
    Nov 2011
    Posts
    25
    n a table we have
    examination id |Profile id as two columns

    101| 20
    105| 20
    100| 23
    101| 23
    100| 29
    101| 29
    106| 30
    107| 30
    101| 30
    105| 44
    108| 44
    105| 45
    108| 45
    here exam id 100 and 101 are repeated for profile 23 and 29 but not for profile id for 20 and 30. So in our output 23 and 29 will appear and not 20 and 30.
    Similarly 44 and 45 are duplicates and it should appear in the output

    Out put will be :
    row 1: 23, 29
    row 2: 44, 45
    Can it done using a sql query in DB2.

  6. #6
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    yes, I gave you a lay out for how you would write the query yesterday. Also, asked what you had thus far for your assignment. We are not in the habit of doing your homework for you on this site, but we will give advice and point out where you may have an error. If you'd like for one of us to just do it for you, I'm sure someone would accept payment via paypal.

Posting Permissions

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