I have three tables to record the relationship of stores (the examples
are not meaningful)----I wonder if join table query can achieve the
following

I want to list all stores that are on '01' relation list in
`table_relation` and (the challenging part) list how many transactions
have happened between '01' and them.

table_store
--------------------------------------
| id | user_name | phone |
---------------------------------------
| 01 | walmart | 12345 |
---------------------------------------
| 02 | target | 23456 |
---------------------------------------
| 03 | bestbuy | 34567 |
---------------------------------------

table_transactions
---------------------------------------
| id | related_id | relation |
---------------------------------------
| 01 | 02 | close |
---------------------------------------
| 01 | 03 | foe |
---------------------------------------
| 03 | 02 | foe |
---------------------------------------
| 02 | 01 | close |
---------------------------------------
| 02 | 03 | close |
---------------------------------------


table_transactions
-------------------------------------------------------------
| tran_id | buyer_id | provider_id | time |
--------------------------------------------------------------
| 001 | 01 | 02 | 2/17/07|
--------------------------------------------------------------
| 002 | 01 | 02 | 2/18/07|
--------------------------------------------------------------
| 003 | 01 | 0３ | 2/1９/07|
--------------------------------------------------------------
| 003 | 0２ | 01 | 2/1９/07|
--------------------------------------------------------------

************************************************** **
select table_store.id, table_store.user_name, table_store.phone,
table_relation.relation from `table_store`, `table_relation` WHERE
table_relation.id = '01' AND table_relation.related_id =
table_store.id;

with the above query, I can only get (half of what I want):
02 target 23456 close
03 bestbuy 34567 foe
**************************************************

**************************************************
What I really want is the above info plus statistics from
table_transactions: i.e., the transactions happend for
--01 as buyer and the other id as provider
--the other id as buyer and 01 as provider.

02, target, 23456, close, 2 (transactions that '01' as buyer and
'02' is provider), 1 (transactions that '02' as buyer and '01' is
provider)
03 bestbuy 34567 foe, 1(transactions that '01' as buyer and '03' is
provider), 0 (transactions that '03' as buyer and '01' is provider )
**************************************************