Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: help needed in wring query for this logic in db2

    Hi,
    the following is my requirement.

    Table A
    Key value
    100 31
    102 23

    Table B

    Key M_Value

    100 1A105
    101 1A153

    Condition :
    User will provide the key. Using the key i have to check the Table A
    "value" column. If the "value" = 31 then i will have to fetch the Table B "M_Value" for the key.

    simply i can say if the user gives the key as 100 then it should check for the value column of Table A and if it has 31 then i have to get the respective key data from column M_value of TableB that is 1A105.

    Is is possible to do this requirement in suing query rather than writing stored procedure.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What should you return, if the value is not 31?

    Another point: I think that the sample data is too little.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sure:

    Code:
    select b.m_value 
    from tableA as a
    inner join tableB as b on (a.key = b.key)
    where a.key = ? and a.value = 31
    Andy

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the "value" = 31 then i will have to fetch the Table B "M_Value" for the key.
    You may want to use OUTER JOIN and something like "ON a.value = 31 AND b.key = a.key".

  5. #5
    Join Date
    Apr 2011
    Posts
    27
    Hi tonkuma,

    if the value is not 31 then return null.

    can i use this :


    select
    b.[Key],
    b.M_Value
    from
    TableB b
    where
    b.[Key] = @userKey
    and exists (select * from TableA a where a.[Key] = b.[Key] and a.[Value] = 31)

    please help me

  6. #6
    Join Date
    Apr 2011
    Posts
    27
    Thanks andy.

    any more suggestions please

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT a.key
         , b.m_value
      FROM TableA a
      LEFT OUTER JOIN
           TableB b
       ON  a.value = 31
       AND b.key   = a.key
     WHERE a.key = ?

  8. #8
    Join Date
    Apr 2011
    Posts
    27
    Thanks a lot friends.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ...
           TableA a
      LEFT OUTER JOIN
           TableB b
       ON  a.value = 31
       AND b.key   = a.key
    As a basic rule,
    ON condition in outer join gives selection condition of rows from inner table(I mean right table of left outer join or left table of right outer join),
    even if the condition includes columns of outer table(left table of left outer join or right table of right outer join).

    For example:
    the following query joins all rows of TableB to a row of TableA which satisfied the (ON) condition "a.value = 31".
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH TableA(Key , value) AS (
    VALUES
      (100 , 31)
    , (102 , 23)
    )
    , TableB(Key , M_Value) AS (
    VALUES
      (100 , '1A105')
    , (101 , '1A153')
    )
    SELECT a.key AS a_key
         , a.value
         , b.key AS b_key
         , b.m_value
      FROM TableA a
      LEFT OUTER JOIN
           TableB b
       ON  a.value = 31
    /* AND b.key = a.key */
    -- WHERE a.key = 101
    ;
    ------------------------------------------------------------------------------
    
    A_KEY       VALUE       B_KEY       M_VALUE
    ----------- ----------- ----------- -------
            100          31         100 1A105  
            100          31         101 1A153  
            102          23           - -      
    
      3 record(s) selected.
    Last edited by tonkuma; 04-08-11 at 21:35.

Posting Permissions

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