Select query with join in ORACLE

Question

I have four tables as

table map

old_ac      fin_ac
07063501    3435853
12522201    1737631
14195701    2515535

table gam

fin_ac    cif      acid    
3435853  49275      121     
1737631  78254      131
2515535  84000      141

table ast

acid despatch
121     E
131     E
141     E

table phone

cif         email
49275       xyz.com 
78254       xyz.com
84000       xyz.com

I want to generate a report for which the query I have written is

SELECT b.cif,
       a.old_ac,
       a.fin_ac,
       d.email,
       c.despatch
from map a,
       gam  b,
       ast  c,
       phone d 
where a.fin_ac = b.fin_ac 
and b.acid = c.acid 
and d.cif = b.cif
and  c.DESPATCH  in ('A','B','D','E')

The result I get is

CIF             OLD_AC          FIN_AC          EMAIL   DISPATCH       
10800049275 0140007063501   9080003435853   xyz.com     E
10800078254 0140012522201   9080001737631   xyz.com     E
10800084000 0140014195701   9080002515535   xyz.com     E

Now For some of the rows in map table, FIN_AC is NULL. I want to retrieve all the rows from map even if fin_ac is NULL. I tried using LEFT OUTER JOIN but it didn't work.

The problem is in the IN clause. you won't get the FIN_AC with NULL values even with the left join because the correspondig DESPATCH will be NULL. — Alessandro Vecchio
– Alessandro Vecchio, Commented Mar 8, 2017 at 10:00

APC · Accepted Answer · 2017-03-08 11:45:28Z

4

The problem with the outer join is that if MAP.FIN_AC is null it doesn't join to GAM, and all of your other joins depend on GAM. So there are no records retrieved from AST which means DESPATCH is null. However your WHERE clause restricts on values of DESPATCH, so NULL will fail the IN criteria.

One solution is to make AST an inline query, with the filter there. I have used the ANSI SQL 92 syntax because I think it makes outer joins easier to understand.

"I want all map records where map.fin_ac is null but gam.fin_ac is not null. Actually in gam fin_ac is never null "

Not sure quite what you want, but perhaps a FULL OUTER JOIN between MAP and GAM will provide the results you're looking for? This is a solution which is only available with the ANSI 92 syntax.

SELECT b.cif,
       a.old_ac,
       a.fin_ac,
       d.email,
       c.despatch
from map a 
     full outer join gam  b 
          on  a.fin_ac = b.fin_ac 
      left join ( select acid, despatch from ast
                  where  DESPATCH  in ('A','B','D','E') )  c 
          on  b.acid = c.acid 
      left join phone d
         on  d.cif = b.cif

edited Mar 8, 2017 at 11:45

answered Mar 8, 2017 at 10:17

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vishal5364 Over a year ago

It is also giving rows where despatch is not equal to ('A','B','D','E')

Ming · Accepted Answer · 2017-03-08 11:42:00Z

1

WITH
    map as (
        SELECT '07063501' AS old_ac, '3435853' AS fin_ac FROM DUAL
        UNION ALL
        SELECT '12522201' AS old_ac, '1737631' AS fin_ac FROM DUAL
        UNION ALL
        SELECT '14195701' AS old_ac, '2515535' AS fin_ac FROM DUAL
        UNION ALL
        SELECT '14195702' AS old_ac, NULL AS fin_ac FROM DUAL
    ),
    gam as (
        SELECT '3435853' AS fin_ac, 49275 AS cif, 121 AS acid FROM DUAL
        UNION ALL
        SELECT '1737631' AS fin_ac, 78254 AS cif, 131 AS acid FROM DUAL
        UNION ALL
        SELECT '2515535' AS fin_ac, 84000 AS cif, 141 AS acid FROM DUAL
    ),
    ast as (
        SELECT 121 AS acid, 'E' AS despatch FROM DUAL
        UNION ALL
        SELECT 131 AS acid, 'E' AS despatch FROM DUAL
        UNION ALL
        SELECT 141 AS acid, 'E' AS despatch FROM DUAL
    ),
    phone as (
        SELECT 49275 AS cif, 'xyz.com' AS email FROM DUAL
        UNION ALL
        SELECT 78254 AS cif, 'xyz.com' AS email FROM DUAL
        UNION ALL
        SELECT 84000 AS cif, 'xyz.com' AS email FROM DUAL
    )
SELECT
    b.cif,
    a.old_ac,
    a.fin_ac,
    d.email,
    c.despatch
FROM
    map a,
    gam b,
    ast c,
    phone d
WHERE
    a.fin_ac = b.fin_ac(+) AND
    b.acid = c.acid(+) AND
    b.cif = d.cif(+) AND
    NVL(c.DESPATCH, 'A') IN ('A','B','D','E')

edited Mar 8, 2017 at 11:42

answered Mar 8, 2017 at 10:12

Ming

2111 silver badge6 bronze badges

6 Comments

APC Over a year ago

Your solution assumes the FIN_AC exists in GAM but not in MAP. That's not what the says. " For some of the rows in map table, FIN_AC is NULL . I want to retrieve all the rows from map even if fin_ac is NULL.". So they want MAP records returned when MAP.FIN_AC is null. Your query doesn't handle that circumstance.

Ming Over a year ago

Oh, you are right, I'm sorry I missed that. Thank you.

Vishal5364 Over a year ago

@APC To be clear, I want all map records where map.fin_ac is null but gam.fin_ac is not null. Actually in gam fin_ac is never null

APC Over a year ago

So why does your sample query select MAP.FIN_AC not GAM.FIN_AC? Please post some sample data which accurately reflects the problem you're trying to solve and the outcome you want to achieve. As it is seems like we've been wasting our time trying to solve the wrong problem.

APC Over a year ago

I repeat my pleas for you to provide a complete set of sample data which demonstrates your requirement, including the records you want excluded from the result set as well as the ones you want included

|

Collectives™ on Stack Overflow

Select query with join in ORACLE

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related