question

CLyde avatar image
CLyde asked

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-resultsloop
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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