Results 1 to 2 of 2

Thread: tricky query

  1. #1
    Join Date
    Feb 2004
    Posts
    143

    Unanswered: tricky query

    Suppose I have 2 tables called TAB_A and TAB_B.

    TAB_A has the following data

    var_id
    ----------
    OTHV_PROD_VAR_ID
    #AACU001
    #AAUDALL001
    #ABIOFED001
    #ABOTOX001
    #ACCS001
    #ACHEMDR002
    #ACHIRO002
    #ACHIRO003
    #ACHIRO004
    #ACOSMET002
    #ACUSALL001
    #ADME003
    #ADME005
    #ADME006
    #ADME016


    Tab_B has the following data :
    STU_ID VARB_ID
    --------------------------------------
    001 #ADME003
    001 BIOTOX
    001 #ADME016
    002 #ACHIRO003
    002 #AAUDALL001
    003 #ABIOFED001
    003 #ABOTOX001
    003 #ACCS001
    004 #ACCS001
    004 CHEMO
    004 CLIN

    For each VAR_ID in Tab_B, there are multiple varb_id. I looking for a query that returns me all the STU_ID from Tab_B for which all its variables are in the Tab_A.

    In this case, the query should return me
    STU_ID VARB_ID
    --------------------------------------
    002 #ACHIRO003
    002 #AAUDALL001
    003 #ABIOFED001
    003 #ABOTOX001
    003 #ACCS001

    because all the variables for 002 and 003 are there in the other table.
    It should not return me 001 and 004 because all its variables are not there in the other table(though some are there)

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    These are the stu_id's that have "variables" that are in Tab_B but not in Tab_A

    Code:
    select stu_id from tab_b
    where not exists
    ( select null
      from   tab_a
      where  tab_a.var_id = tab_b.varb_id
    )
    The ones you want are therefore:

    Code:
    select *
    from   tab_b
    where stu_id not in
    ( select stu_id from tab_b
      where not exists
      ( select null
        from   tab_a
        where  tab_a.var_id = tab_b.varb_id
      )
    )

Posting Permissions

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