JOIN - Need to include a.tkt_num = b.tkt_num And (a.tkt_num not = b.tkt_num)

Analytics
Enthusiast

JOIN - Need to include a.tkt_num = b.tkt_num And (a.tkt_num not = b.tkt_num)

Hi,

Can you please assist me with the following Join Query… I’d like my query to always show everything from the pedw_tmp.tickets_ccb_tmp even if there’s nothing in the pedw.tickets b, and pedw_tmp.sadf_rec40_stg.  They (.b, .c) can be NIL or BLANK if there are no tkt_num matches but I must have all data from tickets_ccb_tmp a.

Thank you for your help!

CURRENT QUERY:

SELECT  a.postdate, a.cc_typ_cde, a.sale_crcy_cde, a.billing_crcy_cde, a.tkt_num, a.sale_amt, a.billing_amt, b.tkt_cpn_ind, b.true_orig, b.true_dest, c.tkt_rpt_src_typ, c.CPN1, c.CPN2, c.CPN3, c.CPN4 FROM pedw_tmp.tickets_ccb_tmp a JOIN  (SELECT b.tkt_num,  b.tkt_cpn_ind, b.true_orig, b.true_dest FROM pedw.tickets b  WHERE b.postdate > '2012-01-01'   GROUP BY 1,2,3,4  ) b ON a.tkt_num = b.tkt_num  JOIN  (SELECT     c.tkt_num, c.tkt_rpt_src_typ, max((CASE WHEN c.tkt_cpn_num = '1' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde  end)) as CPN1, max((CASE WHEN c.tkt_cpn_num = '2' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN2, max((CASE WHEN c.tkt_cpn_num = '3' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN3, max((CASE WHEN c.tkt_cpn_num = '4' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN4 FROM pedw_tmp.sadf_rec40_stg c  WHERE c.postdate > '2012-01-01'   GROUP BY 1,2 ) c  ON a.tkt_num = c.tkt_num  WHERE a.sale_crcy_cde = 'USD'  AND a.billing_crcy_cde = 'USD' AND a.cc_typ_cde IN ('ca','vi')  AND a.postdate = '2012-08-10' ORDER BY 1,2,3,4,5;














CURRENT QUERY OUTPUT                      
pedw_tmp.tickets_ccb_tmp a pedw.tickets b pedw_tmp.sadf_rec40_stg
POSTDATE CC_

TYP

_CDE
SALE

_CRCY

_CDE
BILLING

_CRCY

_CDE
TKT_NUM SALE

_AMT
BILLING

_AMT
TKT

_CPN

_IND
TRUE

_ORIG
TRUE

_DEST
TKT

_RPT

_SRC

_TYP
CPN1 CPN2 CPN3 CPN4
2012/08/10 VI USD USD 1X205XXXX582 $23.63 $23.63 SSVV YYZ LGA BSP  YYZ  LGA   LGA  YYZ   ? ?
2012/08/10 VI USD USD 1X205XXXX590 $23.63 $23.63 SSSS YLW LAX BSP  YLW  YVR   YVR  LAX   LAX  YVR   YVR  YLW  
***                            
***tkt_num - 1X205XXXX085 Is Missing/dropped as there is no data in SADF_rec40 or in Tickets            

Desired final output to look something like this…(I need all the tkts from ccb_tmp a to Balance to a Aug 10 Report)

POSTDATE













  CC_

TYP

_CDE
SALE

_CRCY

_CDE
BILLING

_CRCY

_CDE
TKT_NUM SALE

_AMT
BILLING

_AMT
TKT

_CPN

_IND
TRUE

_ORIG
TRUE

_DEST
TKT

_RPT

_SRC

_TYP
CPN1 CPN2 CPN3 CPN4
2012/08/10 VI USD USD 1X205XXXX582 $23.63 $23.63 SSVV YYZ LGA BSP  YYZ  LGA   LGA  YYZ   ? ?
2012/08/10 CA USD USD 1X205XXXX085 $22.50 $22.50 NIL or Blank NIL or Blank NIL or Blank NIL or Blank NIL or Blank NIL or Blank NIL or Blank NIL or Blank
2012/08/10 VI USD USD 1X205XXXX590 $23.63 $23.63 SSSS YLW LAX BSP  YLW  YVR   YVR  LAX   LAX  YVR   YVR  YLW  

 

Here’s the  Data  from the 3 Sources:










All 3 tkts in pedw_tmp.tickets_ccb_tmp a        
POSTDATE CC_TYP_CDE SALE_CRCY_CDE BILLING_CRCY_CDE TKT_NUM SALE_AMT BILLING_AMT
2012/08/10 VI USD USD 1X205XXXX582 $23.63 $23.63
2012/08/10 CA USD USD 1X205XXXX085 $22.50 $22.50
2012/08/10 VI USD USD 1X205XXXX590 $23.63 $23.63
             
pedw.tickets b   (1X205XXXX085 tkt_num is not in pedw.tickets b)    
TKT_NUM TKT_CPN_IND TRUE_ORIG TRUE_DEST      
1X205XXXX582 SSVV YYZ LGA      
1X205XXXX590 SSSS YLW LAX      
             
             
pedw_tmp.sadf_rec40_stg (1X205XXXX085 tkt_num is not in pedw_tmp.sadf_rec40_stg c)  
TKT_NUM TKT_RPT_SRC_TYP SEG_ORIG_AP_CDE SEG_DEST_AP_CDE      
1X205XXXX582 BSP  YYZ   LGA        
1X205XXXX582 BSP  LGA   YYZ        
1X205XXXX590 BSP  YLW   YVR        
1X205XXXX590 BSP  YVR   LAX        
1X205XXXX590 BSP  LAX   YVR        
1X205XXXX590 BSP  YVR   YLW        
             
1 REPLY
Junior Supporter

Re: JOIN - Need to include a.tkt_num = b.tkt_num And (a.tkt_num not = b.tkt_num)

>>"Can you please assist me with the following Join Query… I’d like my query to always show everything from the pedw_tmp.tickets_ccb_tmp even if there’s nothing in the pedw.tickets b, and pedw_tmp.sadf_rec40_stg.  They (.b, .c) can be NIL or BLANK if there are no tkt_num matches but I must have all data from tickets_ccb_tmp a."

Three words: LEFT OUTER JOIN.

Cheers.

Carlos.