sql - How to check how many times a record is repeated in different tables

Question

I have two tables here:

Table 1:

process_id customer_id
16         1
21         1
22         1

Table 2:

process_id customer_id
16         1
16         1
22         1

I would like to check how many times each row in table 1 is repeated in table 2.

For example, row 1 in table 1 is repeated 2 times in table 2, row 2 repeated 0 times and row 3 repeated 1 time. I'm not sure how to loop through each row in table 1 and get this result.

Answer 1

As I understood, this is what you are asking for:

select table1.process_id,table1.customer_id,count(table2.process_id) as table2count
from table1 left outer join table2 on table1.process_id==table2.process_id and table1.customer_id=table2.customer_id
group by table1.process_id,table1.customer_id;