Get all rows from the 1st table WHERE its details table (2nd table) has rows with 'Pending' status

Question

I have 2 tables. The 1st table contains the transactions, the 2nd table contains the items that each row in the 1st table has.

1st table:

ID      Customer     Total
----------------------------------
1        Chris        $100
2        John         $249
3        Kim           $20

2nd table:

ID           Item               Status
---------------------------------------
1            Phone              Pending
1            Computer           Pending
3            Calculator         Released

I want to be able to select all the rows from the 1st table that have 'Pending' items in the 2nd table.

As a result, I would like to get an output like this:

ID      Customer     Total_Pending_Count
----------------------------------------
1       Chris            2

Thank you so much.

Answer 1

You could use the following join approach:

SELECT t1.ID, t1.Customer, t2.cnt AS Total_Pending_Count
FROM table1 t1
INNER JOIN (
    SELECT ID, COUNT(*) AS cnt
    FROM table2
    WHERE Status = 'Pending'
    GROUP BY ID
) t2
    ON t2.ID = t1.ID
ORDER BY
    t1.ID;

The above would only report those ID having at least one pending record.
If you instead want to view all IDs then use a left join:

SELECT t1.ID, t1.Customer, COALESCE(t2.cnt, 0) AS Total_Pending_Count
FROM table1 t1
LEFT JOIN (
    SELECT ID, COUNT(*) AS cnt
    FROM table2
    WHERE Status = 'Pending'
    GROUP BY ID
) t2
    ON t2.ID = t1.ID
ORDER BY
    t1.ID;

The solution given by @DaleK in the comments, and assuming you only want to report pending IDs:

SELECT t1.ID, t1.Customer, COUNT(*) AS Total_Pending_Count
FROM table1 t1
INNER JOIN table2 t2
    ON t2.ID = t1.ID
WHERE t2.Status = 'Pending'
GROUP BY t1.ID, t1.Customer
ORDER BY t1.ID;