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.

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.

• sql
• sql-server

• Is id the correlation column? – Dale K Commented Feb 15 at 9:46
• @DaleK The ID is the primary key of the 1st table so yes it's the correlation column. – chris_techno25 Commented Feb 15 at 9:47
• @DaleK I also thought that initially, but the OP appears to want the count of pending records. For that, we need some kind of aggregation. – Tim Biegeleisen Commented Feb 15 at 9:49
• I would think something like LEFT OUTER JOIN or JOIN, COUNT, HAVING. I usually put the SQL in my mind in the question but right now I have no idea about the correct way to do it. – chris_techno25 Commented Feb 15 at 9:52
• I highly recommend using numeric codes to abstract the recording of sequences of values such as Status, rather than using literal strings. This way they can easily be queried for a range of statuses much more efficiently than a list of strings to match. It also supports changing the displayed value by editing the lookup table without having to edit the data records. This is particularly useful when converting a database to another language or supporting multiple languages simultaneously. – Galaxiom Commented Feb 16 at 22:40

1 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;