help needed in wring query for this logic in db2

born2achieve · #1 (**permalink**) 04-08-11, 12:26

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.

tonkuma · #2 (**permalink**) 04-08-11, 12:34

What should you return, if the value is not 31?

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

ARWinner · #3 (**permalink**) 04-08-11, 12:36

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

tonkuma · #4 (**permalink**) 04-08-11, 12:48

Quote:

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".

born2achieve · #5 (**permalink**) 04-08-11, 12:58

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

born2achieve · #6 (**permalink**) 04-08-11, 12:59

Thanks andy.

any more suggestions please

tonkuma · #7 (**permalink**) 04-08-11, 13:05

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 = ?

born2achieve · #8 (**permalink**) 04-08-11, 13:06

Thanks a lot friends.

tonkuma · #9 (**permalink**) 04-08-11, 17:59

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech