I want to compare the database data in the php.
This is my table
hospital_payment_data table
id | date | cash_amount_received ---------------------------------- 1 2020-04-01 7000 2 2020-04-29 1000 3 2020-04-29 2000 4 2020-04-29 3000 5 2020-04-29 4000 6 2020-04-29 5000 7 2020-04-29 6000 8 2020-04-29 70000
cash_receipt_publish table
id | date | amount ---------------------------------- 1 2020-04-29 1000 2 2020-04-29 2000 3 2020-04-29 3000 4 2020-04-29 4000 5 2020-04-29 5000 6 2020-04-29 5000 7 2020-04-29 7000
This is my sql syntax
SELECT ifnull(hospital_payment_data.id,'-') AS pg_id, ifnull(hospital_payment_data.date,'-') AS pg_date, ifnull(hospital_payment_data.cash_amount_received,'-') AS pg_amount, ifnull(cash_receipt_publish.id,'-') AS van_id, ifnull(cash_receipt_publish.date,'-') AS van_date, ifnull(cash_receipt_publish.amount,'-') AS van_amount FROM hospital_payment_data LEFT JOIN cash_receipt_publish ON hospital_payment_data.id = cash_receipt_publish.id UNION ALL SELECT ifnull(hospital_payment_data.id,'-') AS pg_id, ifnull(hospital_payment_data.date,'-') AS pg_date, ifnull(hospital_payment_data.cash_amount_received,'-') AS pg_amount, ifnull(cash_receipt_publish.id,'-') AS van_id, ifnull(cash_receipt_publish.date,'-') AS van_date, ifnull(cash_receipt_publish.amount,'-') AS van_amount FROM hospital_payment_data RIGHT JOIN cash_receipt_publish ON hospital_payment_data.id = cash_receipt_publish.id WHERE hospital_payment_data.id IS NULL limit 0
in this web page result
I Want result
If the date and the amount match or the amount match, I would like to express it as False by comparing the table with the table in cash_receipt_public.
id | cash_amount_received | amount| result ----------------------------------------------------- 1 7000 7000 true 2 1000 1000 true 3 2000 2000 true 4 3000 3000 true 5 4000 4000 true 6 5000 5000 true 7 6000 null false 8 10000 null false 9 null 5000 false
Advertisement
Answer
I think you can use a full join
;
select coalesce(hpd.cash_amount_received, cpr.amount), coalesce(hpd.date, cpr.date), (case when hpd.id is not null and crp.id is not null then 'true' else 'false' end) from (select hpd.*, row_number() over (partition by date, cash_amount_received order by id) as seqnum from hospital_payment_data hpd ) hpd full join (select crp.*, row_number() over (partition by date, amount order by id) as seqnum from cash_receipt_publish crp ) crp on hpd.date = crp.date and hpd.cash_amount_received = crp.amount and hpd.seqnum = crp.seqnum;
EDIT:
In MariaDB, you can use:
select amount, min(date), (count(*) = 2) from ((select hpd.date, cash_amount_received as amount, row_number() over (partition by cash_amount_received order by id) as seqnum from hospital_payment_data hpd ) union all (select crp.date, amount, row_number() over (partition by amount order by id) as seqnum from cash_receipt_publish crp ) ) x group by amount, seqnum