CODEWITHSUNDEEP
sqloracle-database
Novice
Novice

Reputation: 308

SQL Query Left Outer Join with specific record

I have query like this

SELECT P9_IR259_FI, FX_IR259_SYS_PROD_TYP, FX_IR259_SLS_POS, FX_IR259_SLS_CHNL, PX_IR259_OFC_CDE, FX_IR259_SPR_OFC_CDE, FX_IR259_NAME, SUPERNAME, SUPERCDE, SUPERPOS 
FROM IR259 K 
     LEFT OUTER JOIN
         (SELECT FX_IR259_NAME AS SUPERNAME ,FX_IR259_SLS_POS AS SUPERPOS, PX_IR259_OFC_CDE AS SUPERCDE 
         FROM IR259 M 
         WHERE P9_IR259_FI = 75214 AND FX_IR259_SLS_POS = 2 AND FX_IR259_NAME LIKE 'ME%'
         ) ON K.FX_IR259_SPR_OFC_CDE = SUPERCDE 
 WHERE 
    FX_IR259_SLS_POS = '1' 
    AND FX_IR259_SLS_CHNL = 'BRO' 
    AND F9_IR259_CRE_TMS > 00000000000000000 
ORDER BY FX_IR259_SLS_POS, FX_IR259_SPR_OFC_CDE, FX_IR259_SLS_CHNL

but result seems not correct means, i want to show SUPERNAME like 'ME%' only

enter image description here

the result show the SUPERNAME with like 'ME%' but other records have show too.

Thanks for Advice

Upvotes: 0

Views: 41

Answers (3)

anwaar_hell
anwaar_hell

Reputation: 776

SELECT P9_IR259_FI, FX_IR259_SYS_PROD_TYP, FX_IR259_SLS_POS, FX_IR259_SLS_CHNL, PX_IR259_OFC_CDE, FX_IR259_SPR_OFC_CDE, FX_IR259_NAME, SUPERNAME, SUPERCDE, SUPERPOS 
FROM 
(select * from IR259 where SUPERNAME like 'ME%') K 
     LEFT OUTER JOIN
         (SELECT FX_IR259_NAME AS SUPERNAME ,FX_IR259_SLS_POS AS SUPERPOS, PX_IR259_OFC_CDE AS SUPERCDE 
         FROM IR259 M 
         WHERE P9_IR259_FI = 75214 AND FX_IR259_SLS_POS = 2 AND FX_IR259_NAME LIKE 'ME%'
         ) ON K.FX_IR259_SPR_OFC_CDE = SUPERCDE 
 WHERE 
    FX_IR259_SLS_POS = '1' 
    AND FX_IR259_SLS_CHNL = 'BRO' 
    AND F9_IR259_CRE_TMS > 00000000000000000 
ORDER BY FX_IR259_SLS_POS, FX_IR259_SPR_OFC_CDE, FX_IR259_SLS_CHNL

Upvotes: 0

Adish
Adish

Reputation: 691

You only need to add another condition in the where clause

SELECT P9_IR259_FI, FX_IR259_SYS_PROD_TYP, FX_IR259_SLS_POS, FX_IR259_SLS_CHNL, PX_IR259_OFC_CDE, FX_IR259_SPR_OFC_CDE, FX_IR259_NAME, SUPERNAME, SUPERCDE, SUPERPOS 
FROM IR259 K 
    LEFT OUTER JOIN
         (SELECT FX_IR259_NAME AS SUPERNAME ,FX_IR259_SLS_POS AS SUPERPOS, PX_IR259_OFC_CDE AS SUPERCDE 
          FROM IR259 M 
         WHERE P9_IR259_FI = 75214 AND FX_IR259_SLS_POS = 2 AND FX_IR259_NAME LIKE 'ME%'
         ) ON K.FX_IR259_SPR_OFC_CDE = SUPERCDE 
 WHERE 
    FX_IR259_SLS_POS = '1' 
    AND FX_IR259_SLS_CHNL = 'BRO' 
    AND F9_IR259_CRE_TMS > 00000000000000000 
    AND SUPERNAME LIKE 'ME%'
ORDER BY FX_IR259_SLS_POS, FX_IR259_SPR_OFC_CDE, FX_IR259_SLS_CHNL

Upvotes: 1

iliketolearn
iliketolearn

Reputation: 710

You need to add another filter to the where clause.

WHERE 
    FX_IR259_SLS_POS = '1' 
    AND FX_IR259_SLS_CHNL = 'BRO' 
    AND F9_IR259_CRE_TMS > 00000000000000000 
    AND SUPERNAME LIKE 'ME%'

Upvotes: 1

Related Questions