LEFT JOIN not return right value

Question

I am trying to join 3 tables with it foreign keys, but for some reason one of the return value from the query doesn't return correct when I use LEFT JOIN for the 3 tables but I removed the one that didn't return correct and tried to query it separately it does below is the query for the 3 tables with LEFT JOIN:

SELECT ROUND(COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END)*500/2) AS current_balance, 
       COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END) AS patient_subscribed, 
       COUNT(CASE WHEN patient_registration.payment_status = 0 THEN 1 ELSE NULL END) AS patient_unsubscribed 
FROM hospital_information 
LEFT JOIN patient_registration ON patient_registration.hospital_id = hospital_information.id 
WHERE hospital_information.id = 3

According to the above I get the correct return for current_balance = 1000,patient_subscribed = 2 and patient_unsubsribed = 21 but when I tried to add another LEFT JOIN I get wrong return, see below:

SELECT ROUND(COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END)*500/2) AS current_balance,
       COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END) AS patient_subscribed, 
       COUNT(CASE WHEN patient_registration.payment_status = 0 THEN 1 ELSE NULL END) AS patient_unsubscribed,
       SUM(payment.price) AS total_generated 
FROM hospital_information 
LEFT JOIN payment ON hospital_information.id = payment.hospital_id
LEFT JOIN patient_registration ON hospital_information.id = patient_registration.hospital_id 
WHERE hospital_information.id = 3

I get wrong response as

current_balance	patient_subscribed	patient_unsubsribed	total_generated
1000	4	42	23000

instead of total_generated = 1000 but when I tried it seperated as below I got the right answer:

SELECT SUM(payment.price) AS total_generated 
FROM hospital_information 
LEFT JOIN payment ON hospital_information.id = payment.hospital_id WHERE hospital_information.id = 3

Clearly there are multiple patient_registration per row of the other tables. You need to pre-aggregate it. Sample data would help — Charlieface
– Charlieface, Commented Aug 28, 2022 at 11:22
The biggest help here would be if you provided example rows for each Table so we can see what the data looks like before you JOIN the Tables together. As Charlieface points out, you likely have a misunderstanding on the cardinality of your Tables and how they relate. — J.D.
– J.D., Commented Aug 28, 2022 at 12:35

mideveloper · Accepted Answer · 2022-08-28 14:33:32Z

I was able to resolve this but LEFT JOIN the SELECT statement separately as below:

SELECT 
* 
FROM(SELECT ROUND(COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END)*500/2) AS current_balance,
    COUNT(CASE WHEN patient_registration.payment_status = 1 THEN 1 ELSE NULL END) AS patient_subscribed,
    COUNT(CASE WHEN patient_registration.payment_status = 0 THEN 1 ELSE NULL END) AS patient_unsubscribed,hospital_information.hospital_name
    FROM hospital_information 
    LEFT JOIN patient_registration ON patient_registration.hospital_id = hospital_information.id
    WHERE hospital_information.id = 3 GROUP BY hospital_information.id) t1 
    INNER JOIN
    (SELECT
    SUM(payment.price)/2 AS total_generated,
    SUM(CASE WHEN MONTH(CURDATE()) = MONTH(payment.created_at) Then payment.price Else 0 End)/2 as current_month
    FROM hospital_information 
    LEFT JOIN payment ON hospital_information.id = payment.hospital_id WHERE hospital_information.id = 3 GROUP BY hospital_information.id) t2

Stack Exchange Network

LEFT JOIN not return right value

1 Answer 1

Your Answer

Hot Network Questions

LEFT JOIN not return right value

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions