sql query

 

//query citicard => C

SELECT
rs_citicard_dts.id
FROM
rs_citicard_dts
Inner Join rs_owners ON rs_owners.custom_num = rs_citicard_dts.cust_no
Inner Join rs_offers ON rs_citicard_dts.offer_id = rs_offers.offer_id AND rs_offers.offer_id = rs_owners.record_id
WHERE
rs_owners.rec_type =  'O' AND
rs_owners.custom_num =  '123456'

//query citicard => C

SELECT
rs_citicard_dts.id,
rs_citicard_dts.offer_id,
rs_citicard_dts.cust_no
FROM
rs_citicard_dts
Inner Join rs_offers ON rs_citicard_dts.offer_id = rs_offers.offer_id AND rs_offers.ctrl1 = rs_citicard_dts.ctrl1
Inner Join rs_owners AS owners ON rs_offers.offer_id = owners.record_id AND owners.ctrl1 = rs_offers.ctrl1 AND owners.custom_num = rs_citicard_dts.cust_no
WHERE
owners.custom_num =  '12346' AND
owners.ctrl1 =  '70' AND
rs_offers.fk_application_id =  '987'

 

//query relationship

SELECT
rs_app_rel_dettls.id,
rs_app_rel_dettls.seq_no
FROM
rs_offers
Inner Join rs_app_rel_dettls ON rs_offers.reln_type = rs_app_rel_dettls.reln_type AND rs_offers.fk_application_id = rs_app_rel_dettls.application_id AND rs_offers.ctrl1 = rs_app_rel_dettls.ctrl1
WHERE
rs_offers.fk_application_id =  '123456'

 

//query citiCard =>A

SELECT
rs_citicard_dts.cust_no,
rs_citicard_dts.offer_id,
rs_citicard_dts.id
FROM
rs_citicard_dts
Inner Join rs_offers ON rs_offers.ctrl1 = rs_citicard_dts.ctrl1 AND rs_offers.offer_id = rs_citicard_dts.offer_id
Inner Join rs_owners ON rs_offers.offer_id = rs_owners.record_id AND rs_offers.ctrl1 = rs_owners.ctrl1 AND rs_owners.custom_num = rs_citicard_dts.cust_no
WHERE
rs_offers.fk_application_id =  '1123456' AND
rs_owners.rec_type =  'O' AND
rs_offers.ctrl1 =  '70'

 

 

//offer table =C

SELECT
rs_offers.id
FROM
rs_offers
Inner Join rs_owners ON rs_offers.offer_id = rs_owners.record_id AND rs_owners.ctrl1 = rs_offers.ctrl1
WHERE
rs_offers.fk_application_id =  '123456' AND
rs_offers.ctrl1 =  '70' AND
rs_owners.rec_type =  'O' AND
rs_owners.custom_num =  '123466'

//relationship ==C

SELECT
rs_app_rel_dettls.id
FROM
rs_app_rel_dettls
Inner Join rs_offers ON rs_app_rel_dettls.reln_type = rs_offers.reln_type AND rs_app_rel_dettls.ctrl1 = rs_offers.ctrl1 AND rs_offers.fk_application_id = rs_app_rel_dettls.application_id
Inner Join rs_owners ON rs_offers.offer_id = rs_owners.record_id AND rs_owners.ctrl1 = rs_offers.ctrl1
WHERE
rs_owners.rec_type =  'O' AND
rs_offers.ctrl1 =  '70'