CODEWITHSUNDEEP
mysqlsqlmariadb
user1082164
user1082164

Reputation: 55

How do I retrieve data from two tables?

I was on the "hospital_payment_data" table. I want to call up the data of the number of data, the cache_account_received sum, and the total_medical_bills sum, and then bring up the mount sum value from the cash_recipit_rowtable to express. What should I do?

  1. hospital_payment_data

    enter image description here

  2. cash_receipt_row

    enter image description here

I want result

enter image description here

However, sending the following queries results in the following:

SELECT 
  COUNT(*) as total,  
  SUM(cash_amount_received) AS sum_cash_amount_received, 
  COUNT(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , total_medical_bills, NULL)
  ) as obligatory_issue, 
  SUM(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , cash_amount_received, NULL)
  ) as sum_obligatory_issue, 
  SUM(amount) AS sum_amount 
FROM (
  SELECT total_medical_bills, cash_amount_received, amount 
  FROM hospital_payment_data, cash_receipt_row
) AS a

wrong result

enter image description here

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

You can also simplify your counting logic in MySQL. There is no need for IF() or a subquery:

SELECT COUNT(*) as total,  
       SUM(cash_amount_received) AS sum_cash_amount_received, 
       SUM( total_medical_bills >= 100000 AND 
            obligatory_issue <> 0
          ) as obligatory_issue, 
       SUM(CASE WHEN total_medical_bills >= 100000
                THEN cash_amount_received
           END) as sum_obligatory_issue, 
       SUM(amount) AS sum_amount 
FROM hospital_payment_data hpd JOIN
     cash_receipt_row crr
     ON hpd.id = crr.id;

You'll notice that where conditional logic is needed, then this uses the standard SQL construct, CASE, rather than IF.

Upvotes: 0

Digvijay S
Digvijay S

Reputation: 2705

Try this. 

SELECT 
  COUNT(*) as total,  
  SUM(cash_amount_received) AS sum_cash_amount_received, 
  COUNT(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , total_medical_bills, NULL)
  ) as obligatory_issue, 
  SUM(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , cash_amount_received, NULL)
  ) as sum_obligatory_issue, 
  SUM(amount) AS sum_amount 
FROM (
  SELECT total_medical_bills, cash_amount_received, amount 
  FROM hospital_payment_data, cash_receipt_row 
WHERE hospital_payment_data.id = cash_receipt_row.id
) AS a

Upvotes: 1

Related Questions