如何使用其中的一个公共字段从数据库连接三个表。（Mysql）

问题描述：

These are three tables

1. table `p_transactions` has following fields

(`txn_id`, `txn_uid`, `txn_bid_no`, `txn_date`, `txn_desc`, `txn_amt`, `txn_fee`, `txn_mode`, `txn_status`, `txn_mdate`)

2 . table `p_game_results` has following fields.

(`id`, `game_id`, `game_combo`, `game_combo_hr`, `cdate`, `mdate`)

3. Table `p_game_room_results` has following fields

 (`id`, `game_id`, `room_id`, `txn`, `round`, `result`, `score`, `cdate`, `mdate`)

I would like to join them using their txn id as a common field.

here's something I tried. but not sure, I'm sure its wrong .

$sql    = "SELECT * FROM " . $prefix . "_user_game_results".$prefix."_game_room_results".$prefix."_transactions WHERE". $prefix . "_user_game_result.uid"='$prefix."_game_room_results.id"'. and .$prefix."_transactions.txn_uid"='$prefix."_game_room_results.uid"'"";
        $result = $this->sql_fetchrowset($this->sql_query($sql));

Thanks.

答

    select * 
    from p_transactions as p_t INNER JOIN p_game_results as p_g_r
    ON p_t.txn_id = p_g_r.id
    INNER JOIN
    p_game_room_results as p_g_r_r
    ON p_g_r_r.game_id = p_g_r.game_id;

The following alias are used in INNER JOIN

   p_transactions as p_t
   p_game_results as p_g_r
   p_game_room_results as p_g_r_r

I suggest you to go through these useful SQL Joins Tutorial

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

http://en.wikipedia.org/wiki/Join_%28SQL%29

答

This should do it:

select * from p_transactions pt 
inner join p_game_room_results pgrr on pgrr.txn=pt.txn_id 
inner join p_game_results pgr on pgr.game_id=pgrr.game_id

Joined all tables by their common fields. p_game_room_results and p_game_results were joined by game id since that seems to be the common field.

如何使用其中的一个公共字段从数据库连接三个表。（Mysql）

相关推荐