Untitled

SELECT tdmm.display_text AS hbpd_customer_category , tdmm.display_text as hbpd_customer_segment FROM tib_chassis.tib_disp_msg_master tdmm
JOIN (select tpcm.association_id AS hbpd_party_id , CONCAT('FNAME','MNAME','LNAME') as hbpd_customer_name , CONCAT('FNAME','MNAME','LNAME') as hbpd_subscriber_name From  tibtcare.tcare_party_contact_map tpcm) ON tpcm.ASSOCIATION_ID=pm.PARTY_ID AND tpcm.CONTACT_TYPE='BILLING' and (tpcm.END_DATE is null or tpcm.END_DATE >= NOW())