Select query should return Null column row

Billa007 · #1 (**permalink**) 01-02-12, 04:25

Hi;

For below query, we have to handle the NULL rows also

Code:

SELECT t1.p_loc
     , t1.p_nor
     , t1.p_det
     , t2.tran_no
     , t2.tran_code
     , t3.tran_des AS tran_des1
     , COALESCE(
          t3_2.tran_des
        , t3  .tran_des
       ) AS tran_des2
 FROM
       table1 t1
 left outer JOIN
       table2 t2
   ON  t2  .tran_no   =  t1.p_loc
and    t2.p_nor= t1.p_nor
 INNER JOIN
       table3 t3
   ON  t3  .tran_item =  t2.tran_no
 LEFT  OUTER JOIN
       table3 t3_2
   ON  t2  .tran_no   <> t2.tran_code
   AND t3_2.tran_item =  t2.tran_code

Table1

Code:

p_loc   p_NOR        p_det
A11     1234         aaa
A11     3334         aaa
B11     1311         WWW

Table2

Code:

tran_no  p_nor   tran_code
A11      1234    A11
A11      3334    
B11      1311    S11

Table3

Code:

tran_ITEM      tran_des
A11              sout
B11              west
S11              EAST

Expected Result set

Code:

p_LOC         p_nor        p-det   tran_no   tran-code   tran_des1   tran-des2 
A11           1234          aaa       A11      A11          SOUT       SOUT
A11           3334          aaa       A11                   SOUT       
B11           1311          WWW       B11      S11          west       EAST

Please help.

tonkuma · #2 (**permalink**) 01-02-12, 05:32

Would you supply sample/test data of table1, table2 and table3 with one of the following ways, if you want my response.

(1) CREATE TABLE statements and INSERT statements.
or
(2) WITH common-table-expressions.

You are free to answer my request, or not.

Billa007 · #3 (**permalink**) 01-02-12, 05:43

HI;

Please find the query with CTE

Code:

WITH
 TABLE1(p_loc , p_nor , p_det) AS (
VALUES
  ( 'A11' , 1234 , 'aaa' )
, ( 'A11' , 3334 , 'aaa' )
, ( 'B11' , 1311 , 'WWW' )
)
, TABLE2(tran_no ,p_nor , tran_code) AS (
VALUES
  ( 'A11' ,1234, 'A11' )
, ( 'A11' ,3334, '   ' )
, ( 'B11' ,1311, 'S11' )
)
, TABLE3(tran_item , tran_des) AS (
VALUES
  ( 'A11' , 'sout' )
, ( 'B11' , 'west' )
, ( 'S11' , 'EAST' )
)
SELECT t1.p_loc
     , t1.p_nor
     , t1.p_det
     , t2.tran_no
     , t2.tran_code
     , t3.tran_des AS tran_des1
     , COALESCE(
          t3_2.tran_des
        , t3  .tran_des
       ) AS tran_des2
 FROM
       table1 t1
 left outer JOIN
       table2 t2
   ON  t2  .tran_no   =  t1.p_loc
and    t2.p_nor= t1.p_nor
 INNER JOIN
       table3 t3
   ON  t3  .tran_item =  t2.tran_no
 LEFT  OUTER JOIN
       table3 t3_2
   ON  t2  .tran_no   <> t2.tran_code
   AND t3_2.tran_item =  t2.tran_code

tonkuma · #4 (**permalink**) 01-02-12, 06:02

How about simple LEFT OUTER JOIN?

Code:

SELECT t1.p_loc
     , t1.p_nor
     , t1.p_det
     , t2.tran_no
     , t2.tran_code
     , t3.tran_des   AS tran_des1
     , t3_2.tran_des AS tran_des2
 FROM
       table1 t1
 left outer JOIN
       table2 t2
   ON  t2  .tran_no   =  t1.p_loc
and    t2.p_nor= t1.p_nor
 INNER JOIN
       table3 t3
   ON  t3  .tran_item =  t2.tran_no
 LEFT  OUTER JOIN
       table3 t3_2
   ON  t3_2.tran_item =  t2.tran_code
 ORDER BY
       p_loc
     , p_nor
;
------------------------------------------------------------------------------

P_LOC P_NOR       P_DET TRAN_NO TRAN_CODE TRAN_DES1 TRAN_DES2
----- ----------- ----- ------- --------- --------- ---------
A11          1234 aaa   A11     A11       sout      sout     
A11          3334 aaa   A11               sout      -        
B11          1311 WWW   B11     S11       west      EAST     

  3 record(s) selected.

Billa007 · #5 (**permalink**) 01-02-12, 07:37

Hi;

I was using the below query

Code:

SELECT t1.p_loc
     , t1.p_nor
     , t1.p_det
     , t2.tran_no
     , t2.tran_code
     , t3.tran_des   AS tran_des1
     , t3_2.tran_des AS tran_des2
 FROM
       table1 t1
 left outer JOIN
       table2 t2
   ON  t2  .tran_no   =  t1.p_loc
and    t2.p_nor= t1.p_nor
 INNER JOIN
       table3 t3
   ON  t3  .tran_item =  t2.tran_no
 LEFT  OUTER JOIN
       table3 t3_2
   ON  t3_2.tran_item =  t2.tran_code
 ORDER BY
       p_loc
     , p_nor
;

But it returns ' no rows found'

so that i have added LEFT OUTER JOIN instead of INNER join on below part

Code:

and    t2.p_nor= t1.p_nor
 /* INNER JOIN  */
LEFT OUTER JOIN
       table3 t3
   ON  t3  .tran_item =  t2.tran_no

it retuns result set like below

Code:

p_LOC         p_nor        p-det   tran_no   tran-code   tran_des1   tran-des2 
A11           1234          aaa       A11      A11          SOUT       SOUT
A11           3334          aaa                             
B11           1311          WWW       B11      S11          west       EAST

Pl help

Billa007 · #6 (**permalink**) 01-02-12, 08:27

Working fine... Please ignore the above post...thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech