1

I am trying to query two different columns from two different table where I could count the number of certain column. I could do it in two separate query but I think it would create a slowdown in the future. What I want is a single query where I could display both of those counts in a single query only.

I tried using two separated select statements but I think is not good, I also tried using Union All, but the results are not what I expected.

    upload_monitoring (12 Columns)
    upm_FileName    | upm_Status
    ----------------+--------------
    Monitoring_0608 | Distributed
    Monitoring_0607 | Distributed
    Monitoring_0606 | Distributed
    Monitoring_0605 | Uploaded
    (100 rows)

    distribute_monitoring (7 Columns)
    dist_ProductName   | dist_Status
    -------------------+--------------
    Monitoring_0608    | Pending
    Monitoring_0607    | Pending
    Monitoring_0606    | Pending
    Monitoring_0605    | Touched
    (100 rows)

I tried with these:

$query2 = "
    SELECT
        COUNT(upm_Status) AS total_DistItems
    FROM
        upload_monitoring
    WHERE
        upm_Status = 'Distributed'                                   
    AND
        upm_FileName = '$upm_FileName'
    ";

$result2 = mysqli_query($connection, $query2);
$fetchResult2 = mysqli_fetch_assoc($result2);
$total_DistItems = $fetchResult2['total_DistItems'];

$query3 = "
    SELECT
        COUNT(dist_Status) AS total_PendItems
    FROM
        distribute_monitoring
    WHERE
        dist_Status = 'Pending'
    AND
        dist_Product = '$upm_FileName'
    ";

$result3 = mysqli_query($connection, $query3);
$fetchResult3 = mysqli_fetch_assoc($result3);
$total_PendItems = $fetchResult3['total_PendItems'];

I also tried with these one

$query2 = "                                              
    SELECT
        upm_Status,
        COUNT(upm_Status) AS total_DistItems
    FROM
        upload_monitoring
    WHERE
        upm_Status = 'Distributed' 
    AND
        upm_FileName = '$upm_FileName'
    UNION ALL
    SELECT
        dist_Status,
    COUNT(dist_Status) AS total_PendItems
    FROM
        distribute_monitoring
    WHERE
        dist_Status = 'Pending'
    AND
        dist_Product = '$upm_FileName'
    ";

$result2 = mysqli_query($connection, $query2);

however the result is

    upm_Status  | total_DistItems
    ------------+--------------
    Distributed | 34
    Pending     | 12

What I expect the result to be is like this one.

    upm_Status  | total_DistItems | dist_Status | total_PendItems
    ------------+-----------------+-------------+-----------------
    Distributed | 34              | Pending     | 12
Stonesteel
  • 59
  • 1
  • 10

1 Answers1

0

Here is one method:

SELECT u.*, d.*
FROM (SELECT 'Distributed' as upm_Status,
             COUNT(*) AS total_DistItems
      FROM upload_monitoring
      WHERE upm_Status = 'Distributed' AND
            upm_FileName = ?
     ) u CROSS JOIN
    (SELECT 'Pending' as dist_Status, COUNT(dist_Status) AS total_PendItems
     FROM distribute_monitoring
     WHERE dist_Status = 'Pending' AND
           dist_Product = ?
     ) d;

Note that I replaced the $upm_filename with ?. This indicates that you should be using parameters to pass values into the query, rather than munging the query string with such values. Your method puts you at risk for unexpected syntax errors and SQL injection attacks.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Please set a better example by close-voting duplicates instead of answering them. – jpmc26 Jun 08 '19 at 13:46
  • @gordon-linoff, $upm_filename is actually a result fetched from the very first query, also this project is for internal company use only, therefore SQL injection is not applicable for this one. Anyways I tried your method and it works, I also removed the part "Distributed as" and "Pending as" as there are also different statuses to query. Thanks for your help. :) – Stonesteel Jun 09 '19 at 01:43