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 )
**************************************************

On 14 Aug, 06:37, newbie <mitbb...@yahoo.com> wrote:
> 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 )
> **************************************************

Read up on the JOIN syntax