How to Efficiently Query Multiple Columns from Different Tables in SQL?

Optimizing Multi-Table, Multi-Column SQL Queries

Retrieving data from multiple tables often presents challenges, especially when needing to aggregate values from various columns. A recent support case highlighted a user's difficulty in accurately counting values across multiple columns in two tables. Their initial attempt, using nested subqueries, yielded incorrect results.

The flawed query looked like this:

SELECT * from
    ( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )e,
    (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS like '%ong%' and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN ) a,
    (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS like '%rtde%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )b,
    (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS like '%pol%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )c,
    (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS like '%para%' and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )d

The solution lies in using conditional aggregation within a single query. This streamlined approach calculates multiple columns based on specified conditions, providing accurate results. The improved query is:

SELECT DAY_IN, COUNT(*) AS arr,
        SUM(IF(PAT_STATUS like '%ong%', 1, 0)) AS ONG1,
        SUM(IF(PAT_STATUS like '%rtde%', 1, 0)) AS RTED,
        SUM(IF(PAT_STATUS like '%pol%', 1, 0)) AS POL1,
        SUM(IF(PAT_STATUS like '%para%', 1, 0)) AS para
FROM t_hospital 
WHERE DAY_IN between @start_check and @finish_check 
    and RES_DATE between @start_res and @finish_res  
    and ID_daily_hos =@daily_hos
GROUP BY DAY_IN

This revised query efficiently retrieves the necessary data, eliminating the errors associated with the original, overly complex structure. The key is consolidating the calculations into a single, well-structured query using conditional SUM() statements.

The above is the detailed content of How to Efficiently Query Multiple Columns from Different Tables in SQL?. For more information, please follow other related articles on the PHP Chinese website!