How to loop query2 on the results from query1

Question

question

CLyde asked Dec 14, '20

How to loop query2 on the results from query1

Newbie question

I have a database called phone that has 2 tables, phone_numbers and phone_history..

tables: Phone_numbers Phone_history

columns: phone_numbers_id , phone_number phone_history_id, number_FK, timestamp

(Phone_history.number_FK is Phone_numbers.phone_numbers_id).

table Phone_numbers contains:

12 4085551212

13 4081234567

12167876 1234567890

table Phone_history contains:

1 12 2020-12-08

2 12 2020-11-23

3 12 2020-09-07

4 13 2020-11-15

5 12167876 2020-10-10

:

171 12167876 2019-12-31

If I run the following query, (Query 1), I get a result set of 1 number:

Query 1

"SELECT phone_numbers_id FROM phone_numbers WHERE phone_number = 1234567890;"

A number, (121678), is returned which is the phone_numbers_id from the Phone_numbers table, (as one might expect).

I can run a query to give me a count of the number of times the number_FK exists in Phone_History, (166).

Query 2

"SELECT COUNT(*) FROM phone_history WHERE number_FK = 121678; " (Yields the 166 number above.)

My question is - is there a way I can use the results from query 1 as a loop index of some sort so that I run query 2 against each of the 166 entries in the phone_history column of the Phone_history table automagically? What I would like is something like this as output:

Count number

3 4085551212

1 4081234567

166 1234567890

@bettybotter2@charter.net

query-results loop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-12-14T08:23:17Z

Kev Riley answered Dec 14, '20

You want to join the 2 tables on their common data - in your case this is the phone number, and then do the count :

select count(*) as 'Count', Phone_numbers.phone_number 
from Phone_numbers  
join phone_history  on phone_history.number_FK = Phone_numbers.phone_numbers_id
group by Phone_numbers.phone_number

If you have more numbers in Phone_numbers than in phone_history, you might want to look at an outer join

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question