I am trying to write a query but getting a duplicate row for each contract_id. Can you please advise how I can avoid getting duplicate rows?
SELECT
ev.transaction_date,
ev.contract_id,
ev.created_at AS BCTESG_event_created_date,
ev.resp_event_id,
ev.transaction_type,
ev.beneficiary_id,
(be.given_name || ' ' || be.surname) AS bene_name,
CASE WHEN be.province = 'BC' THEN 'no_prob_with_bene_address' ELSE 'bene_address_needs_update' END AS bene_address,
cb.applied_for_bctesg_on,
cb.applied_for_clb_on,
cb.apply_for_low_income,
cb.apply_for_provincial_grant,
be.grant_identification_document_id,
cs.subscriber_id,
cs.is_primary,
(s.given_name || ' ' || s.surname) AS subscriber_name,
CASE WHEN s.province = 'BC' THEN 'no_prob_with_subscriber_address' ELSE 'subscriber_address_needs_update' END AS subscriber_address
FROM
so_lunchmoney.bctesg_transactions AS ev
LEFT JOIN
so_lunchmoney.contract_beneficiaries AS cb ON ev.contract_id = cb.contract_id
LEFT JOIN
so_lunchmoney.beneficiaries AS be ON cb.beneficiary_id = be.id
LEFT JOIN
so_lunchmoney.contract_subscribers AS cs ON ev.contract_id = cs.contract_id
INNER JOIN
so_lunchmoney.subscribers AS s ON cs.subscriber_id = s.id
I am expecting to get one single row for each contract_id
New contributor
prash is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.