How to create a SQL Union Loop

June 30, 2022

I am kinda stuck here trying to create a query, this is a simplified version of what I am looking for but should be sufficient to be able to work as a basis for the logic required.

Essentially what is going on, I am going to be running this query for 100s of different customerIDs that are associated with a parent customer. but I need to be able to grab all the documents for that customer and group them by their receiverID. This would then get me the number of documents that the customer has sent to the receiver.

I think the way to do this would be some kind of UNIONED loop that will take the customers ids that have the customer parent ID and use that as the value in the primary loop.

For example here would be an example table that should give a good understanding of requirements

Document Table

DocumentID	CustomerID	receiverID	customerParentID
1	1	25	8
2	1	25	8
3	1	26	8
4	1	26	8
5	1	27	8
6	4	25	8
7	3	25	8
8	3	26	8
9	2	26	8
10	2	27	8

The result of the table should show up like this.

Number of Documents	CustomerID	ReceiverID
2	1	25
2	1	26
1	1	27
1	4	25
1	3	25
1	3	26
1	2	26
1	2	27

Basically my thoughts right now are to do the following query, this would correctly group the information but would not work for also separating out the customerIDs.

I think I would have to do a nested select in a loop to make this happen.

SELECT 
    COUNT(d.DocumentID), customerID, receiverID
FROM
    document d
WHERE
    d.customerID IN (SELECT 
            d.customerID
        FROM
            document
        WHERE
            customerParentID = 8)
GROUP BY receiverID

The problem with the above query is that it would return the following. which would be incorrect.

Number of Documents	CustomerID	ReceiverID
4	1	25
4	1	26
2	1	27

Thank you all for your time and let me know if you have questions that I can answer to help get a solution to this.

The effect functionality that I want is the following but in a loop.

SELECT 
    COUNT(d.DocumentID), customerID, receiverID
FROM
    document d
WHERE
    d.customerID = '1'
GROUP BY receiverID 
UNION SELECT 
    COUNT(d.DocumentID), customerID, receiverID
FROM
    document d
WHERE
    d.customerID = '2'
GROUP BY receiverID 
UNION SELECT 
    COUNT(d.DocumentID), customerID, receiverID
FROM
    document d
WHERE
    d.customerID = '3'
GROUP BY receiverID 
UNION SELECT 
    COUNT(d.DocumentID), customerID, receiverID
FROM
    document d
WHERE
    d.customerID = '4'
GROUP BY receiverID

>Solution :

Just add "customerID" inside the GROUP BY clause and filter the "customerParentID" if necessary.

SELECT COUNT(d.DocumentID), 
       customerID, 
       receiverID
FROM document d
WHERE customerParentID = 8
GROUP BY customerID,
         receiverID